Slava Rozhnev

create table datavalues (
	id serial primary key,
  	value text -- json or any suitable type
);

create table manydata (
	id bigserial primary key,
  	value_id int,
  	inserted_at timestamp with time zone default current_timestamp,
  	updated_at timestamp,
    foreign key (value_id) references datavalues(id)
);
create index on manydata(value_id);

SELECT records.id, array_agg(name) AS names
FROM records 
LEFT JOIN fields ON fields.id = ANY(records.fields)
GROUP BY records.id;

CREATE TABLE users (
	id serial primary key,
  	name varchar(255)
);

INSERT INTO users VALUES (1, 'John'), (2, 'Sarah');

CREATE TABLE shares (
	id varchar(12) primary key,
  	price numeric
);

INSERT INTO shares VALUES ('TSLA', 781.30), ('AAPL', 105.00);

CREATE TABLE user_shares (
	user_id int,
  	share varchar(12),
  	count int
);

INSERT INTO user_shares VALUES (1, 'TSLA', 5), (2, 'AAPL', 1);

SELECT 
	users.*,
	shares.*,
	user_shares.count,
	user_shares.count * shares.price total
FROM user_shares
JOIN users ON user_shares.user_id = users.id
JOIN shares ON user_shares.share = shares.id
;

select * 
from events
where 
	date > current_timestamp - interval '1 hour'
	and type in ('open', 'open_page_1')
	and not exists (
		select type from events e 
	  	where e.operation_id = events.operation_id
	  		and type not in ('open', 'open_page_1')
	)
;

select id, test_date
from test
where extract(month from test_date) = 1;

INSERT INTO tbl (id, chtoto) VALUES (74291910, array['string'])
ON CONFLICT (id) DO UPDATE SET chtoto=array_append(tbl.chtoto, 'string');
								 
INSERT INTO tbl (id, chtoto) VALUES (74291910, array['string2'])
ON CONFLICT (id) DO UPDATE SET chtoto=array_append(tbl.chtoto, 'string2');

+==========+==================+
| id       | chtoto           |
+==========+==================+
| 74291910 | {string,string2} |
+----------+------------------+

select 
	i.name,
	min(ru) filter (where fi.id = 218) key1,
	min(ru) filter (where fi.id = 219) key2,
	min(ru) filter (where fi.id = 220) key3
from items i
left join item_fields fi on i.id = fi.item_id and fi.id in (218, 219, 220)
group by i.name, item_id

+=======+========+========+========+
| name  | key1   | key2   | key3   |
+=======+========+========+========+
| Item1 | Поле 1 | Поле 2 | Поле 3 |
+-------+--------+--------+--------+

CREATE TABLE platforms (
  id serial,
  name varchar(64),
  date_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO platforms (name) VALUES ('Test platform');

SELECT * FROM platforms;

SELECT id, name, to_char(date_added, 'dd/mm/YYYY') FROM platforms;

select json_build_object(
	'id', users.id,
    'name', users.name,
	'items', jsonb_agg(inventory.name) 
) json_result
from users
left join inventory on inventory.user_id = users.id
where users.id = 1
group by users.id, users.name
;

+============================================================================+
| json_result                                                                |
+============================================================================+
| {"id" : 1, "name" : "User 1", "items" : ["Stuff 1", "Stuff 2", "Stuff 3"]} |
+----------------------------------------------------------------------------+

CREATE TABLE images (
  image_id INTEGER AUTO_INCREMENT PRIMARY KEY, 
  filename VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE tags (
  tag_id INTEGER AUTO_INCREMENT PRIMARY KEY, 
  tag VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE images_tags (
  image_id INTEGER NOT NULL, 
  tag_id INTEGER NOT NULL, 
  PRIMARY KEY (tag_id, image_id), 
  FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
  FOREIGN KEY (image_id) REFERENCES images(image_id)
);

select 
	users.id,
	users.name,
	sum(money.value) as user_value
from users
join money on users.id  = money.user_id
where value > 0
group by users.id, users.name
order by user_value desc;