TIL - 250131

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;