-- STEP 1: Understand the grain
--   "What does ONE ROW in the output represent?"
--   Example: one row = one product per category

-- STEP 2: Build intermediate CTEs
WITH raw_data AS (
    -- Clean or filter source data
    SELECT ...
    FROM source_table
    WHERE conditions
),

intermediate AS (
    -- Apply window function or join
    SELECT
        ...,
        ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col) AS rn
    FROM raw_data
)

-- STEP 3: Filter to final result
SELECT ...
FROM intermediate
WHERE rn = 1;  -- or whatever condition applies

-- STEP 4: Check edge cases in your head:
--   - What if there are NULL values in partition/order columns?
--   - What if a group has only 1 row? (top-2 query returns 1 row per group — is that OK?)
--   - Are ties handled correctly? (RANK vs DENSE_RANK)
--   - What if the date column has gaps?

-- STEP 5: Verbalize to interviewer:
--   "First I'm partitioning by X because each group needs its own ranking..."
--   "I chose DENSE_RANK over RANK because I want ties to share the same rank..."