250424_TIL

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

SQL 문제풀이 복습

Leetcode

2984. Find Peak Calling Hours for Each City
풀이시간 05:12

WITH result
     AS (SELECT city,
                HOUR(call_time) AS "peak_calling_hour",
                Count(*)        AS "number_of_calls"
         FROM   Calls
         GROUP  BY 1,
                   2)
SELECT city,
       peak_calling_hour,
       number_of_calls
FROM   result
WHERE  ( city, number_of_calls ) IN (SELECT city,
                                            Max(number_of_calls)
                                     FROM   result
                                     GROUP  BY 1)
ORDER  BY 2 DESC,
          1 DESC;

2985. Calculate Compressed Mean
풀이시간 01:16

SELECT ROUND(SUM(item_count * order_occurrences) / (SELECT
             SUM(order_occurrences)
                                                    FROM   Orders), 2) AS
       "average_items_per_order"
FROM   Orders;

2986. Find Third Transaction
풀이시간 17:11

SELECT user_id,
       spend            AS "third_transaction_spend",
       transaction_date AS "third_transaction_date"
FROM   (SELECT user_id,
               spend,
               transaction_date,
               RANK()
                 OVER(
                   partition BY user_id
                   ORDER BY transaction_date ASC) AS "rank1",
               LAG(spend)
                 OVER(
                   partition BY user_id
                   ORDER BY transaction_date ASC) AS "lag1",
               LAG(spend, 2)
                 OVER(
                   partition BY user_id
                   ORDER BY transaction_date ASC) AS "lag2"
        FROM   Transactions) result
WHERE  rank1 = '3'
       AND spend > lag1
       AND spend > lag2
ORDER  BY 1 ASC;