๐Ÿ“’ Today I Learn/๐Ÿ˜Š My SQL

SQL Challenge ์„ธ์…˜ #6 ๋งค์ถœ๋ถ„์„

ny:D 2024. 6. 7. 16:22

240607 Today I Learn

Z-์ฐจํŠธ๋กœ ๋งค์ถœ ์ถ”์ด ํ™•์ธํ•˜๊ธฐ

๐Ÿ’ก Z์ฐจํŠธ๋ž€?
๋งค์ถœ๋ถ„์„์„ ์œ„ํ•ด ์‚ฌ์šฉํ•˜๋Š” ์ฐจํŠธ๋กœ, ์›”๋ณ„๋งค์ถœ, ๋งค์ถœ๋ˆ„๊ณ„, ๊ทธ๋ฆฌ๊ณ  ์ด๋™ํ•ฉ๊ณ„๋ฅผ ํ•œ๋ฒˆ์— ํ‘œ์‹œํ•˜๋Š” ๊ทธ๋ž˜ํ”„์ด๋‹ค.

  • ์›”๋ณ„๋งค์ถœ (ํŒŒ๋ž‘)
  • ๋งค์ถœ๋ˆ„๊ณ„ (์ฃผํ™ฉ) : ์ƒ๋Œ€์ ์œผ๋กœ ๋‹จ๊ธฐ์  ์ถ”์ด
  • ์ด๋™ํ•ฉ๊ณ„ (ํšŒ์ƒ‰) : ํ•ด๋‹น ์›” ๊ธฐ์ค€ 1๋…„์น˜ ๋ˆ„์ ํ•ฉ๊ณ„
    • ์žฅ๊ธฐ์  ์ถ”์ด๋ฅผ ์•Œ ์ˆ˜ ์žˆ์Œ
    • ๊ณ„์ ˆ์„ฑ, ํ”„๋กœ๋ชจ์…˜ ๋“ฑ ๋ณ€๋™์„ฑ์„ ์™„ํ™”ํ•ด ์ถ”์„ธ๋ฅผ ๋ณผ ์ˆ˜ ์žˆ์Œ

Z-์ฐจํŠธ ๊ทธ๋ฆฌ๊ธฐ (์‹ค์Šต)

Sales_Transaction ํ…Œ์ด๋ธ”์—์„œ ํ˜ธ์ฃผ, ์ดํƒˆ๋ฆฌ์•„์˜ ์›”๋ณ„ ๋งค์ถœ,  ๋งค์ถœ ๋ˆ„๊ณ„ ๊ตฌํ•˜๊ธฐ 

## 1. ํ˜ธ์ฃผ, ์ดํƒˆ๋ฆฌ์•„์˜ ์›”๋ณ„ ๋งค์ถœ,  ๋งค์ถœ ๋ˆ„๊ณ„ ๊ตฌํ•˜๊ธฐ 
SELECT   Country 
        ,DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m') AS dt_month 
        ,SUM(Price*Quantity) as monthly_sales  
        ,ROUND(
          SUM(SUM(Price*Quantity)) OVER(PARTITION BY Country ORDER BY 
                                 DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
              , 2)  AS country_cumsum                           
FROM Sales_Transaction st 
WHERE Country IN ('Australia','Italy')
GROUP BY Country,dt_month 
ORDER BY Country,dt_month;

Sales_Transaction ํ…Œ์ด๋ธ”์—์„œ ํ˜ธ์ฃผ, ์ดํƒˆ๋ฆฌ์•„์˜ ์›”๋ณ„ ๋งค์ถœ,  ๋งค์ถœ ๋ˆ„๊ณ„, <์ด๋™ ํ•ฉ๊ณ„> ๊ตฌํ•˜๊ธฐ

## ํ˜ธ์ฃผ, ์ดํƒˆ๋ฆฌ์•„์˜ ์›”๋ณ„ ๋งค์ถœ,  ๋งค์ถœ ๋ˆ„๊ณ„, <์ด๋™ ํ•ฉ๊ณ„> ๊ตฌํ•˜๊ธฐ
SELECT   Country 
        ,DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m') AS dt_month 
        ,SUM(Price*Quantity) as monthly_sales  
        ,ROUND(
          SUM(SUM(Price*Quantity)) OVER(PARTITION BY Country ORDER BY 
                                 DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
              , 2)  AS country_cumsum
        ,ROUND(
         SUM(SUM(Price*Quantity)) OVER(PARTITION BY Country
         							   ORDER BY DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
         							   ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
         	, 2) AS moving_cumsum
FROM Sales_Transaction st 
WHERE Country IN ('Australia','Italy')
GROUP BY Country,dt_month 
ORDER BY Country,dt_month;

๐Ÿ“ˆ Z-์ฐจํŠธ ๊ทธ๋ฆฌ๊ธฐ

๋ณดํ†ต SQL์„ ์‚ฌ์šฉํ•ด์„œ ์ฐจํŠธ๋ฅผ ๊ทธ๋ฆฌ๋Š” ๊ฒฝ์šฐ์—๋Š”, SQL๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ์ถ”์ถœํ•˜๊ณ  ์ด๋ฅผ ์—‘์…€์ดํƒ€ ๋‹ค๋ฅธ ์‹œ๊ฐํ™” ํˆด๋กœ ๊ทธ๋ž˜ํ”„ํ™”ํ•œ๋‹ค๊ณ  ํ•œ๋‹ค. ๊ฐ€์žฅ ์ ‘๊ทผํ•˜๊ธฐ ์‰ฌ์šด ์—‘์…€์„ ํ™œ์šฉํ•ด ์ถ”์ถœ๋œ ๋ฐ์ดํ„ฐ ์ค‘ ์ด๋™ ํ•ฉ๊ณ„๊ฐ€ ๋น„๊ต์  ๋ช…ํ™•ํžˆ ๋“œ๋Ÿฌ๋‚˜๋Š” '์ดํƒˆ๋ฆฌ์•„'์˜ ์ผ€์ด์Šค๋ฅผ ์ฐจํŠธ๋กœ ๊ทธ๋ ค๋ดค๋Š”๋ฐ, ์œ„์™€ ๊ฐ™์€ ๋ชจ์–‘์ด ๋‚˜์™”๋‹ค.

์ •ํ™•ํ•œ z ๋ชจ์–‘์˜ ์ฐจํŠธ๋ฅผ ๊ตฌํ•˜๋ ค๋ฉด ์ตœ๊ทผ 2๋…„์น˜ ๋ฐ์ดํ„ฐ๊ฐ€ ํ•„์š”ํ•˜๋‹ค. ๊ทธ๋Ÿฌ๋‚˜ ์œ„ ๊ทธ๋ž˜ํ”„๋Š” ๋ณด๋‹ค์‹œํ”ผ Z-์ฐจํŠธ๋ผ๊ณ  ๋ณด๊ธฐ์—๋Š” ์•„์‰ฌ์šด ๋ชจ์–‘์ด๋‹ค. ๊ทธ ์ด์œ ๋Š” ๋ฐ์ดํ„ฐ์˜ ์–‘์ด ๋ถ€์กฑํ–ˆ๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค. ์ดํƒˆ๋ฆฌ์•„์˜ ๊ฒฝ์šฐ 2018๋…„ 12์›”๋ถ€ํ„ฐ 2019๋…„ 12์›”๊นŒ์ง€ ์ด 13๊ฐœ์›”์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ๋‹ด๊ฒจ์žˆ๋Š”๋ฐ, ๊ทธ๋Ÿฌ๋‹ค๋ณด๋‹ˆ ์ด๋™ํ•ฉ๊ณ„ ์ธก๋ฉด์—์„œ ์ฐจ์ด๊ฐ€ ํฌ์ง€ ์•Š์€๊ฒƒ์ด๋‹ค. 

Sales_Transaction ํ…Œ์ด๋ธ”์—์„œ ์ดํƒˆ๋ฆฌ์•„์˜ ์ œํ’ˆ ์ด๋ฆ„๋ณ„ ์›”๋ณ„ ๋งค์ถœ,  ๋งค์ถœ ๋ˆ„๊ณ„, ์ด๋™ ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ

## ์ดํƒˆ๋ฆฌ์•„์˜ ์ œํ’ˆ ์ด๋ฆ„๋ณ„ ์›”๋ณ„ ๋งค์ถœ,  ๋งค์ถœ ๋ˆ„๊ณ„, ์ด๋™ ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ
SELECT   ProductName
        ,DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m') AS dt_month 
        ,SUM(Price*Quantity) as monthly_sales  
        ,ROUND(
          SUM(SUM(Price*Quantity)) OVER(PARTITION BY ProductName  ORDER BY 
                                 DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
              , 2)  AS country_cumsum
        ,ROUND(
         SUM(SUM(Price*Quantity)) OVER(PARTITION BY ProductName
         							   ORDER BY DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
         							   ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
         	, 2) AS moving_cumsum
FROM Sales_Transaction st 
WHERE Country = 'Italy'
GROUP BY ProductName ,dt_month 
ORDER BY ProductName ,dt_month;

YOY ๋ถ„์„ํ•˜๊ธฐ

๐Ÿ’ก YOY(Year-Over-Year Growth Formula)
์ „๋…„๋Œ€๋น„ ์ฆ๊ฐ๋ฅ ์œผ๋กœ ์•„๋ž˜์™€ ๊ฐ™์ด ๊ณ„์‚ฐํ•  ์ˆ˜ ์žˆ๋‹ค.
→ (์˜ฌํ•ด ๋งค์ถœ - ์ง€๋‚œํ•ด ๋งค์ถœ) / ์ง€๋‚œํ•ด ๋งค์ถœ X 100
→ (Current Year Earnings — Last Year’s Earnings) / Last Year’s Earnings x 100

 

 

SQL Challenge ์„ธ์…˜ #1 ์œˆ๋„์šฐ ํ•จ์ˆ˜, WITH์ ˆ

240521 Today I LearnWindow Function๐Ÿ’ก Window Functionํ–‰๊ณผ ํ–‰์˜ ๊ด€๊ณ„๋ฅผ ์ •์˜ํ•˜๊ธฐ ์œ„ํ•ด ์ œ๊ณต๋˜๋Š” ํ•จ์ˆ˜๋กœ ๋ชจ๋“  ์ปฌ๋Ÿผ์„ ์žƒ๊ณ  ์‹ถ์ง€ ์•Š์„ ๋•Œ ์‚ฌ์šฉํ•œ๋‹ค.# ์œˆ๋„์šฐ ํ•จ์ˆ˜ ๊ธฐ๋ณธ๋ฌธ๋ฒ• SELECT WINDOW_FUNCTION () OVER([PARTITION BY

archivenyc.tistory.com

→ LEAD, LAG ํ™œ์šฉํ•˜๊ธฐ

 

Wayfair SQL Interview Question | DataLemur

Wayfair SQL Interview Question: Calculate the year-on-year growth rate for each product.

datalemur.com

WITH current_year AS (
    SELECT EXTRACT(YEAR FROM transaction_date) yr,
           product_id,
           SUM(spend) curr_year_spend
    FROM user_transactions
    GROUP BY EXTRACT(YEAR FROM transaction_date), product_id
    ),
    current_prev_year AS (
    SELECT *,
          LAG(curr_year_spend) OVER(PARTITION BY product_id ORDER BY product_id, yr) prev_year_spend
    FROM current_year
    )
SELECT *,
      ROUND((curr_year_spend - prev_year_spend)/prev_year_spend*100.0,2) yoy_rate
FROM current_prev_year;

์ฝ”ํ…Œ ์ฒดํ—˜ํ•˜๊ธฐ

 

 

Amazon SQL Interview Question | DataLemur

Amazon SQL Interview Question: Write a query to identify highest-grossing products within each category.

datalemur.com

WITH product_spend2 AS (
    SELECT category,
           product,
          SUM(spend) AS total_spend
    FROM product_spend
    WHERE EXTRACT(YEAR FROM transaction_date)=2022
    GROUP BY category, product
    ),
    product_rank AS (
    SELECT category, product, total_spend,
           RANK() OVER(PARTITION BY category ORDER BY total_spend DESC) rank
    FROM product_spend2
    )
SELECT category, product, total_spend
FROM product_rank
WHERE rank <= 2;