with d as (
select
date,
open_close,
lead(date) over (order by date asc) next_date,
lead(open_close) over (order by date asc) next_open_close
from moex_fin m
) select * from d
where open_close BETWEEN 3.15 * 0.985 AND 3.15 * 1.005
;select * from resume order by id asc;
SELECT
category.id, category.title, ARRAY_AGG(subcategory.title)
FROM category
JOIN subcategory ON subcategory.category_id = category.id
GROUP BY category.id, category.title;SELECT user_id, SUM(confirmed) AS "confirmed_ones"
FROM(
SELECT vr.user_id user_id, COUNT(vr.status) AS confirmed
FROM violation_requests AS vr
AND vr.status IN('confirmed'::incident_status)
AND NOT vr.is_deleted
GROUP BY vr.user_id = 1
UNION
SELECT c.user_id, COUNT(c.status)
FROM complaints AS c
AND c.status IN('confirmed'::incident_status)
AND NOT c.is_deleted
GROUP BY c.user_id
) AS res
GROUP BY user_id;with s as (
select
* ,
row_number() over (partition by name order by event_time asc) -
row_number() over (partition by name, event order by event_time asc) gr
from t
) select
name, event, count(*)
from s
group by name, event, gr
order by count desc
limit 1;create table comments (
"name" varchar,
"commens" varchar,
"like" int,
"date" timestamp
);
insert into comments
select (json_populate_record(null::comments, value)).* from json_array_elements('[
{
"name": "name1",
"commens": "commens1",
"like": 2,
"date": "2022-01-01"
},{
"name": "name2",
"commens": "commens2",
"like": 2,
"date": "2022-01-01"
},{
"name": "name3",
"commens": "commens3",
"like": 2,
"date": "2022-01-01"
}
]');
select * from comments;SELECT to_char(
to_timestamp(parameters->>'time', 'HH24:MI') - (parameters->>'offset' || ' seconds')::interval,
'HH24:MI'
)
FROM reports;SELECT
ru_name
FROM "tokens"
JOIN regions ON regions.id = any (tokens.regions)
WHERE user_id = 5 ;with reg_ids as (
select unnest(regions) reg_id
from tokens where user_id = 5
) select regions.*
from reg_ids
join regions on regions.id = reg_id;create view subscriptions_status as select
id, user_id, created_at, end_at,
case when end_at > now() then 1 else 0 end active
from subscriptions;
select * from subscriptions_status;with objects as (
select idObject
from tbl
where idProperty in (1,4)
group by idObject
having count(distinct idProperty) = 2
)
select * from tbl join objects using(idObject);SELECT
*
FROM
information_schema.columns
WHERE
table_schema = 'schema_name'
AND table_name = 'table_name';create table books (
ID INT,
AUTHOR VARCHAR
);
SELECT
*
FROM
information_schema.columns
WHERE table_name = 'books';SELECT AUTHOR
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
ORDER BY COUNT(AUTHOR) DESC
FETCH FIRST 1 ROWS WITH TIES;UPDATE users x
SET step = 'confirmed'
FROM users y
WHERE x.number = y.number
AND x.number = 1
RETURNING y.number AS number, y.step AS old_step, x.step;