TIL - 250213

리뷰1 : lag()에 대해 간만에 상기시킨 날이다. lag()는 이전의 값을 반환할 수 있다. 반대로 lead()는 이후의 값을 반환한다.

리뷰2 : cte를 활용해서 문제를 풀었는데 다른 사람은 더 쉽게 풀더라. 그래서 그 방식으로 다시 풀어봤다. 생각을 조금 더 하면 간결한 쿼리를 만들 수 있다는 것을 깨달았다.

리뷰3 : 이번에도 cte를 사용하고 서브쿼리까지 썼는데 더 단순하게 문제를 풀 수 있었다. 멀리 돌아가지 말자.

URL : https://datalemur.com/questions/amazon-shopping-spree

SELECT
  user_id
FROM (
  SELECT
    user_id
    ,transaction_date
    ,LAG(transaction_date,1) OVER(PARTITION BY user_id ORDER BY transaction_date) one_day
    ,LAG(transaction_date,2) OVER(PARTITION BY user_id ORDER BY transaction_date) two_day
  FROM transactions
) a
WHERE one_day IS NOT NULL AND two_day IS NOT NULL
;

URL : https://datalemur.com/questions/histogram-users-purchases

WITH cte AS(
  SELECT
    user_id
    ,MAX(transaction_date) tran_date
  FROM user_transactions
  GROUP BY user_id
)

SELECT
  transaction_date
  ,user_id
  ,COUNT(user_id) purchase_count
FROM user_transactions
LEFT JOIN cte 
USING (user_id)
WHERE transaction_date=tran_date
GROUP BY transaction_date, user_id
ORDER BY transaction_date, user_id
;

SELECT
  transaction_date
  ,user_id
  ,COUNT(user_id) purchase_count
FROM user_transactions
WHERE transaction_date in (SELECT MAX(transaction_date) FROM user_transactions GROUP BY user_id)
GROUP BY 1, 2
ORDER BY 1, 2;

URL : https://datalemur.com/questions/alibaba-compressed-mode

WITH cte AS (SELECT MAX(order_occurrences) AS max_occurrences FROM items_per_order)

SELECT 
  md
FROM (
  SELECT
    CASE 
      WHEN order_occurrences = max_occurrences
      THEN item_count ELSE NULL END AS md
  FROM items_per_order,cte
  ) a
WHERE md IS NOT NULL
;

SELECT item_count FROM items_per_order 
WHERE order_occurrences in (SELECT MAX(order_occurrences) FROM items_per_order)