민희의 코딩일지

https://solvesql.com/problems/volleyball-players-in-two-consecutive-olympics/select distinct t1.athlete_id as id, t1.name as namefrom ( select distinct a.id as athlete_id, a.name, g.year as game_year from records as r join events as e on r.event_id = e.id join teams as t on r.team_id = t.id join games as g on r.game_id = g.id join athletes as a on r.athlete_id = a.id where e.event = 'Vol..

https://solvesql.com/problems/mentor-mentee-list/select e.employee_id as mentee_id, e.name as mentee_name, o.employee_id as mentor_id, o.name as mentor_namefrom ( select employee_id, name, department from employees where join_date > date_sub('2021-12-31', interval 3 month) and join_date o.departmentorder by mentee_id, mentor_id;

https://solvesql.com/problems/multiple-medalist/select a.name from ( select a.id, count(distinct team_id) from records as r join games as g on g.id = r.game_id join athletes as a on r.athlete_id = a.id where r.medal is not null and g.year >= 2000 group by a.id having count(distinct team_id) > 1 ) as t join athletes as a on t.id = a.idorder by a.name;

https://solvesql.com/problems/artists-without-artworks/select m.artist_id, m.name from artists as m left join artworks_artists as a on m.artist_id = a.artist_idwhere m.death_year is not null and a.artist_id is null;

https://solvesql.com/problems/characteristics-of-orders/select region as Region, count(distinct(case when category = 'Furniture' then order_id end)) as Furniture, count(distinct(case when category = 'Office Supplies' then order_id end)) as "Office Supplies", count(distinct(case when category = 'Technology' then order_id end)) as Technologyfrom recordsgroup by regionorder by region;

https://solvesql.com/problems/installment-month/select payment_installments, count(distinct order_id) as order_count, min(payment_value) as min_value, max(payment_value) as max_value, sum(payment_value) / count(*) as avg_valuefrom olist_order_payments_datasetwhere payment_type = 'credit_card'group by payment_installments;

https://solvesql.com/problems/redefine-session/WITH t1 AS ( SELECT user_pseudo_id, event_timestamp_kst, LAG(event_timestamp_kst) OVER ( PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst ) AS prev_time FROM ga WHERE user_pseudo_id = 'S3WDQCqLpK'),t2 AS ( SELECT user_pseudo_id, event_timestamp_kst, CASE ..

https://solvesql.com/problems/top-3-bill/ select day, time, sex, total_billfrom ( select day, time, sex, total_bill, dense_rank() over (partition by day order by total_bill desc) as ranking from tips) as t1 where t1.ranking