aeightchill

[SQL50] 577. Employee Bonus (JOIN) SELECT A.name, B.bonusFROM Employee A LEFT JOIN Bonus B ON A.empId = B.empIdWHERE bonus

[SQL50] 1661. Average Time of Process per Machine (JOIN)WITH take_time AS( SELECT machine_id, process_id, SUM(CASE WHEN activity_type = 'start' THEN -1 * timestamp ELSE timestamp END) FROM Activity GROUP BY machine_id, process_id)SELECT machine_id, ROUND(AVG(sum) ::decimal, 3) AS processing_timeFROM take_timeGROUP BY machine_id;WITH ~ AS( ... )WITH AS로 임시 테이블 생성하여 활용https://youngone-kan..

[SQL50] 197. Rising Temperature (JOIN)SELECT A.idFROM Weather A LEFT JOIN (SELECT id, LAG(recordDate, 1) OVER(ORDER BY recordDate) AS yesterday, LAG(temperature, 1) OVER(ORDER BY recordDate) AS prev_temp FROM Weather) B ON A.id = B.idWHERE temperature > B.prev_temp AND B.yesterday = recordDate - 1;LAG이전 행의 값을 가져올 수 있다. SELECT t, val, LAG(val) AS 'lag'FROM seriesO..

[SQL50] 1581. Customer Who Visited but Did Not Make Any Transactions (JOIN)SELECT A.customer_id, SUM(1) AS count_no_transFROM Visits A LEFT JOIN Transactions B ON A.visit_id = B.visit_idWHERE transaction_id IS NULLGROUP BY customer_id;

[SQL50] 1068. Product Sales Analysis I (JOIN)SELECT B.product_name, A.year, A.priceFROM Sales A LEFT JOIN Product B ON A.product_id = B.product_id;

[SQL50] 1378. Replace Employee ID With The Unique Identifier (JOIN)SELECT B.unique_id, A.nameFROM Employees A LEFT JOIN EmployeeUNI B ON A.id = B.id;

[SQL50] 1683. Invalid Tweets (SELECT)SELECT tweet_idFROM TweetsWHERE LENGTH(content) > 15;

[SQL50] 1148. Article Views I (SELECT)SELECT author_id AS idFROM ViewsWHERE author_id = viewer_idGROUP BY author_idORDER BY author_id;

[SQL50] 595. Big Countries (SELECT)SELECT name, population, areaFROM WorldWHERE area >= 3000000 OR population >= 25000000;

[SQL50] 584. Find Customer Referee (SELECT)SELECT nameFROM CustomerWHERE NOT referee_id = 2 OR referee_id IS NULL;