Slava Rozhnev

select
    t.*,
    count(*) over (partition by number_id)
from t order by id;

select *
from tbl
order by row_number() over(partition by user_id order by views desc), views desc;

select 
  date_trunc('month', data), 
  login, 
  value, 
  sum (value) over (
	partition by login,  date_trunc('month', data)
	order by data rows between unbounded preceding and current row
  ) as lag_value 
from 
  t1;

create or replace function trunc (x timestamp) 
returns date 
language plpgsql as $function$ 
begin 
	return date_trunc('year', to_date((to_char(coalesce($1, current_timestamp), 'YYYY-MM-DD')), 'YYYY-MM-DD'));
end
$function$;

ALTER TABLE table_name DROP COLUMN createdAt; -- не правильно

ALTER TABLE table_name DROP COLUMN "createdAt"; -- правильно

create table info (
  ID int generated always as identity, 
  FirstName text, 
  LastName text, 
  Year int2, 
  Mounth int2,
  primary key (FirstName, LastName)
);

INSERT INTO info (FirstName, LastName, Year, Mounth) VALUES ('Дима', 'Скрипов', 1999, 12)
ON CONFLICT (FirstName, LastName) DO UPDATE SET 
	Year = excluded.Year,
	Mounth = excluded.Mounth;

SELECT DISTINCT
	id,
	coalesce(devices.name, call_list.num2)  "client_num",
FROM call_list
left join devices ON devices.name = call_list.num;

with order_files as (
	select 
  		*, 
  		row_number() over (partition by filename order by version desc, created_at desc) rn
  	from files 
  	where user_id = 1
) select id, filename, user_id, version, created_at
from order_files where rn = 1
;

WITH d AS (
SELECT 
	*,
	row_number() over (partition by key order by created desc) rn
FROM move_history
 ) SELECT * 
 FROM d 
 WHERE status = 'COMPLETED' 
 AND rn = 1
 ORDER BY id;

SELECT * FROM tbl
OFFSET floor(random() * (SELECT COUNT(*)	FROM tbl))
LIMIT 1;

select 
	box_id, max(name), max(another_name), "product", 
	sum(coalesce(incoming::numeric, 0)) "incoming",
	sum(coalesce(output::numeric, 0)) "output",
	sum(coalesce(incoming::numeric, 0)) - sum(coalesce(output::numeric, 0)) "balance"
from table1
group by product, box_id
order by box_id;

SELECT MAX(category_id) FROM stories 
WHERE category = ANY(ARRAY['Тест', 'Тест2']);

WITH p AS (
  SELECT
    payments.*,
    extract(EPOCH from (now() - updated_at::timestamp) / 60) AS minutes_passed
  FROM public.payments
) SELECT 
	p.*,
    CASE
        WHEN 60 - minutes_passed > 0
            AND 60 - minutes_passed <= 15 THEN 'expires'
        WHEN 60 - minutes_passed > 15 THEN 'success'
        ELSE 'expired'
    END
    AS exp_status
FROM p;

CREATE TABLE REQUESTS (
  	ID INT, 
  	id_request INT NOT NULL,
        time_request TIME WITH TIME ZONE NOT NULL
);