/*DROP TABLE IF EXISTS teams;
CREATE TABLE teams
(
team_id INT PRIMARY KEY,
team_name VARCHAR(50) NOT NULL
);
DROP TABLE IF EXISTS matches;
CREATE TABLE matches
(
match_id INT PRIMARY KEY,
host_team INT,
guest_team INT,
host_goals INT,
guest_goals INT
);
INSERT INTO teams VALUES(10, 'Give');
INSERT INTO teams VALUES(20, 'Never');
INSERT INTO teams VALUES(30, 'You');
INSERT INTO teams VALUES(40, 'Up');
INSERT INTO teams VALUES(50, 'Gonna');
INSERT INTO matches VALUES(1, 30, 20, 1, 0);
INSERT INTO matches VALUES(2, 10, 20, 1, 2);
INSERT INTO matches VALUES(3, 20, 50, 2, 2);
INSERT INTO matches VALUES(4, 10, 30, 1, 0);
INSERT INTO matches VALUES(5, 30, 50, 0, 1);
--1
with points as(
select * , case when host_goals=guest_goals then 1
when host_goals>guest_goals then 3 else 0 end host ,
case when host_goals=guest_goals then 1
when host_goals<guest_goals then 3 else 0 end guest
from matches)
, final_table as(select match_id,host_team,host from points
union
select match_id, guest_team,guest from points)
select a.team_id,a.team_name,coalesce(sum(b.host),0)total_points from teams a
left join final_table b
on a.team_id=b.host_team
group by a.team_id,a.team_name;
-- 2
with cte1 as(
select match_id, host_team ,host_goals,guest_goals from matches
union all
select match_id,guest_team, guest_goals,host_goals from matches
)
select t.team_id,t.team_name,
coalesce(sum( case when c.host_goals=c.guest_goals then 1 when c.host_goals>c.guest_goals then 3
else 0 end
) ,0)
total_points
from teams t
left join cte1 c
on t.team_id=c.host_team
group by t.team_id,t.team_name
order by total_points desc;
*/
-------------------------2---------------------
scripts:
CREATE TABLE orders (
order_id INT,
order_date DATE,
user_id INT
);
INSERT INTO orders VALUES
(101, '2024-01-01', 1),
(102, '2024-01-05', 1),
(103, '2024-01-01', 2),
(104, '2024-01-20', 2),
(105, '2024-02-01', 3),
(106, '2024-02-03', 3),
(107, '2024-02-10', 3),
(108, '2024-03-01', 4),
(109, '2024-03-08', 4),
(110, '2024-04-01', 5),
(111, '2024-05-10', 6),
(112, '2024-05-10', 6);
select * from orders;
with cte as(
select *, datediff( day,first_value(order_date) over (partition by user_id order by order_date desc) ,
first_value(order_date) over (partition by user_id order by order_date asc)
from orders
)
select
Telegram: DB developers channel