30일 약속으로 못한 만큼 오늘 더 함
리뷰 : MYSQL기준 EXTRACT 안에서 계산 불가능, timestampdiff(단위,작은 날, 큰 날)
URL : https://datalemur.com/questions/sql-avg-review-ratings

SELECT
EXTRACT(MONTH FROM submit_date) mth
,product_id
,ROUND(AVG(stars),2)
FROM reviews
GROUP BY 1,2
ORDER BY 1,2
;
URL : https://datalemur.com/questions/sql-well-paid-employees

SELECT a.employee_id, a.name
FROM employee a
JOIN employee b
ON a.manager_id = b.employee_id
AND a.salary > b.salary
;
URL : https://datalemur.com/questions/click-through-rate

SELECT
app_id,
ROUND(
100.0 * SUM (CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) /
SUM (CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END),2
) ctr
FROM events
WHERE EXTRACT(YEAR FROM timestamp) = 2022
GROUP BY 1
;
URL : https://datalemur.com/questions/second-day-confirmation

SELECT user_id
FROM emails e
JOIN texts t USING (email_id)
WHERE signup_action = 'Confirmed'
AND TIMESTAMPDIFF(DAY, signup_date, action_date) = 1;