Advanced SQL: Window Functions, CTEs, and Subqueries Questions

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.

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.

Approach
I would build a date spine, which is a table of every calendar date in the reporting range, then cross join it to the product list. That guarantees zero-sales days are present. After that, I would left join the daily sales fact and use a running sum partitioned by product and month.

WITH spine AS (
  SELECT d::date AS dt
  FROM generate_series(date '2025-01-01', date '2025-01-31', interval '1 day') AS g(d)
), daily AS (
  SELECT product_id, sale_date, SUM(revenue) AS revenue
  FROM sales_fact
  GROUP BY product_id, sale_date
), base AS (
  SELECT p.product_id, s.dt
  FROM products p
  CROSS JOIN spine s
)
SELECT
  b.product_id,
  b.dt,
  COALESCE(d.revenue, 0) AS revenue,
  SUM(COALESCE(d.revenue, 0)) OVER (
    PARTITION BY b.product_id, DATE_TRUNC('month', b.dt)
    ORDER BY b.dt
  ) AS mtd_revenue
FROM base b
LEFT JOIN daily d
  ON d.product_id = b.product_id
 AND d.sale_date = b.dt;

Reference data needed

• A product dimension.
• A calendar or date spine table, or a generated date series.

Why it works
Partitioning by month resets the running total at each boundary, and the spine ensures missing product-day combinations still show as zero.

Approach
I would first dedupe to one row per user per active day, because multiple events on the same day should not create extra streak length. Then I would use the gaps-and-islands pattern. An island is a run of consecutive dates. A simple way to label each island is to subtract the row number from the date, because consecutive dates keep the same shifted value.

WITH active_days AS (
  SELECT DISTINCT user_id, activity_date
  FROM raw_events
  WHERE activity_date IS NOT NULL
), islands AS (
  SELECT
    user_id,
    activity_date,
    activity_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY activity_date
    ) * INTERVAL '1 day') AS island_key
  FROM active_days
)
SELECT user_id,
       MIN(activity_date) AS start_date,
       MAX(activity_date) AS end_date,
       COUNT(*) AS streak_length
FROM islands
GROUP BY user_id, island_key;

Handling bad data
If there are known missing source days, I would join to a data-quality calendar and exclude those outage dates before building streaks. That prevents a pipeline failure from looking like a user break.

Worked example
For dates 1, 2, 3, 5, the first three share one island. The 5 starts a new one. If day 4 was a confirmed outage, I would not count it as a true break.

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.