Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Approach
First aggregate at the raw line-item grain, then rank the product totals within each region. A line item is one row of product revenue, so summing those rows avoids double counting order-level facts. For the tie rule, use DENSE_RANK() or RANK() on the final product totals, then keep ranks <= 3.

WITH product_revenue AS (
  SELECT
    region,
    product_id,
    SUM(line_revenue) AS revenue
  FROM raw_line_items
  GROUP BY region, product_id
), ranked AS (
  SELECT
    region,
    product_id,
    revenue,
    DENSE_RANK() OVER (
      PARTITION BY region
      ORDER BY revenue DESC
    ) AS revenue_rank
  FROM product_revenue
)
SELECT region, product_id, revenue
FROM ranked
WHERE revenue_rank <= 3;

Why this is correct

• The aggregation happens before ranking, so each product appears once per region.
• DENSE_RANK() includes every product tied at the cutoff.
• If you also need order-level facts, aggregate them in a separate CTE at order grain and join the finished summaries, not the raw tables.

Worked example
If a region has revenues 100, 90, 90, 80, the ranks are 1, 2, 2, 3. Filtering <= 3 returns all four products, which matches the tie requirement.

How I would investigate
I would compare the query profile, not just the SQL text. A CTE, or common table expression, is a named subquery. On some warehouses it is inlined, and on others it may be materialized or repeatedly scanned. I would look for repeated reads of the same large table, lost predicate pushdown, and big intermediate results.

First checks

• Run EXPLAIN or the warehouse query profile.
• See whether the same base table is scanned more than once.
• Check whether filters are applied after large joins instead of before them.
• Compare row counts at each step to find the explosion point.

What I would change first
If a suspicious CTE is referenced once, I would inline it or fold it into the parent query. If it is reused multiple times, I would stage the filtered result in a temp table or a materialized table so the expensive work happens once.

Rule of thumb
Readability is good, but if separate CTEs each reread a billion-row fact table, the query gets slower. The fix is usually to push filters earlier, keep only needed columns, and avoid recomputing the same join or aggregation in multiple branches.

Approach
I would model the customer segment as a slowly changing dimension, meaning a history table with valid_from and valid_to timestamps. Then I would do an as-of join, which matches each order to the dimension row that was valid when the order happened, not the current row.

SELECT
  DATE_TRUNC('month', o.order_ts) AS month,
  d.segment,
  SUM(o.revenue) AS revenue
FROM orders o
JOIN dim_customer_history d
  ON o.customer_id = d.customer_id
 AND o.order_ts >= d.valid_from
 AND o.order_ts <  d.valid_to
GROUP BY 1, 2;

Why this is auditable

• The join is driven by the order timestamp, so reclassifying a customer today does not rewrite history.
• I would keep immutable raw orders, the dimension history, and the monthly mart as separate layers.
• I would store the load batch or snapshot date so finance can reproduce the exact closing report.

Late data handling
If an order is backfilled after month close, I would rerun the affected month from raw facts, but only against the dimension history that was effective at the order time. That keeps the numbers consistent and explainable during reconciliation.

What I would look at
Spill to disk usually means the engine ran out of memory for a large sort or window step. Since the query has several window functions, I would inspect whether they share the same PARTITION BY and ORDER BY clauses. If not, the engine may sort the same billion-row fact table multiple times.

Tuning steps

• Keep only the columns needed before the window step.
• Filter as early as possible, especially on date ranges.
• Align window definitions so the engine can reuse one sort.
• Pre-aggregate to the lowest grain that still preserves the result.
• Check table clustering or partitioning on the hottest filters, such as day.

Example
If one window is over (customer_id, day) and another is over (customer_id, country, day), I would ask whether the business result really needs both raw row-level windows. If a daily summary is enough, I would stage one row per customer, country, and day first, then run the windows on that smaller set.

Why this helps
You are not changing the answer, just reducing the amount of data that must be sorted and carried through each window calculation. That is usually the fastest way to stop spills.

Clarify first
I would ask whether the product manager wants a time-based window or a calendar window. A 168-hour window means the last 7 times 24 hours from each event timestamp. A calendar window means the current day plus the previous 6 calendar days in the store's business timezone. Those are not the same at midnight boundaries.

Implementation choices

• If they want 168 hours, use a timestamp window.
• If they want calendar days, aggregate by date first, then roll up daily counts.

-- Calendar-day version
WITH daily AS (
  SELECT
    store_id,
    CAST(order_ts AS date) AS order_date,
    COUNT(*) AS orders
  FROM orders
  GROUP BY store_id, CAST(order_ts AS date)
)
SELECT
  store_id,
  order_date,
  SUM(orders) OVER (
    PARTITION BY store_id
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_day_orders
FROM daily;

Boundary example
An order at 2025-01-08 00:05 UTC is inside the calendar-day window for Jan 8 through Jan 2, but in a strict 168-hour window it depends on the exact timestamp cutoff. I would document that choice in the metric definition so analysts get the same answer.