SQL | Consecutive Numbers, Product Price at a Given Date

https://leetcode.com/problems/consecutive-numbers/description/

---

Consecutive Numbers

# 연속해서 3번 이상 나온 숫자

WITH a AS(
    SELECT
        *,
        # 바로 다음 행의 위치
        LEAD(num, 1) OVER(ORDER BY id, num) AS num1,
        # 다음 다음 행의 위치
        LEAD(num, 2) OVER(ORDER BY id, num) AS num2
    FROM
        Logs)
SELECT
    DISTINCT num AS ConsecutiveNums
FROM
    a
WHERE
    (num = num1) AND (num = num2) 
    # 바로 다음과 다음 다음 행과 기존 행이 일치한다 = 3개의 숫자가 일치

💡 LEAD('칼럼명', '가져올 행의 위치')

---

https://leetcode.com/problems/product-price-at-a-given-date/description/

---

Product Price at a Given Date

1) 2019-08-16을 기준으로 내림차순 순위를 매김

# 2019년 8월 16일 모든 제품 가격
# 가격이 변경되기 전에는 10

SELECT 
        *,
        RANK() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rk
    FROM
        Products
    WHERE
        change_date <= '2019-08-16'

2) 순위가 1인 칼럼만 조회 

WITH a AS(
    SELECT 
        *,
        RANK() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rk
    FROM
        Products
    WHERE
        change_date <= '2019-08-16')
SELECT
    a.product_id,
    a.new_price AS price
FROM
    a
WHERE rk = 1

3) 2019-08-16 기준 가격이 변경되기 않은 값 조회

SELECT
    product_id,
    10 AS price
FROM
    Products
WHERE
    product_id NOT IN(
SELECT
    product_id
FROM 
    Products
WHERE change_date <= '2019-08-16')

4) 합치기

WITH a AS(
    SELECT 
        *,
        RANK() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rk
    FROM
        Products
    WHERE
        change_date <= '2019-08-16')
SELECT
    a.product_id,
    a.new_price AS price
FROM
    a
WHERE rk = 1

UNION

SELECT
    product_id,
    10 AS price
FROM
    Products
WHERE
    product_id NOT IN(
SELECT
    product_id
FROM 
    Products
WHERE change_date <= '2019-08-16')