250215_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

---

Leetcode

2228. Users With Two Purchases Within Seven Days
풀이시간 06:14

SELECT DISTINCT p1.user_id
FROM   Purchases p1
       JOIN Purchases p2
         ON p1.user_id = p2.user_id
            AND p1.purchase_id <> p2.purchase_id
            AND ABS(DATEDIFF(p2.purchase_date, p1.purchase_date)) <= 7
ORDER  BY 1;

2238. Number of Times a Driver Was a Passenger
풀이시간 02:24

SELECT r1.driver_id,
       COUNT(DISTINCT r2.ride_id) AS "cnt"
FROM   Rides r1
       LEFT JOIN Rides r2
              ON r1.driver_id = r2.passenger_id
GROUP  BY 1;

2292. Products With Three or More Orders in Two Consecutive Years
풀이시간 10:06

SELECT DISTINCT product_id
FROM   (SELECT product_id,
               oyear,
               oyear - ROW_NUMBER()
                         OVER(
                           partition BY product_id
                           ORDER BY oyear) AS "group_id"
        FROM   (SELECT product_id,
                       Year(purchase_date) AS "oyear",
                       Count(order_id)     AS "cnt"
                FROM   Orders
                GROUP  BY 1,
                          2) r
        WHERE  cnt >= 3) r2
GROUP  BY product_id,
          group_id
HAVING Count(*) >= 2;

2298. Tasks Count in the Weekend
풀이시간 06:11

2트(241112) 때는 피벗테이블을 만들어서 풀었는데,
그럴 필요까지도 없이 그냥 바로 count와 case when을 조합해서 구할 수 있다.

SELECT COUNT(DISTINCT CASE
                        WHEN DAYOFWEEK(submit_date)%7 IN ( 1, 0 ) THEN task_id
                      end) AS 'weekend_cnt',
       COUNT(DISTINCT CASE
                        WHEN DAYOFWEEK(submit_date)%7 IN ( 2, 3, 4, 5, 6 ) THEN
                        task_id
                      end) AS 'working_cnt'
FROM   Tasks;

2308. Arrange Table by Gender
풀이시간 03:23

2트(241112) 때는 gender의 순서를 case when으로 구했지만
오늘은 dense_rank로 gender의 문자열 길이(char_length)를 써서 구했다.

SELECT user_id,
       gender
FROM   (SELECT user_id,
               gender,
               ROW_NUMBER()
                 OVER(
                   partition BY gender
                   ORDER BY user_id)                  AS "row_id",
               DENSE_RANK()
                 OVER(
                   ORDER BY CHAR_LENGTH(gender) DESC) AS "row_id2"
        FROM   Genders) r
ORDER  BY row_id,
          row_id2;