Advanced SQL: Window Functions, CTEs, and Subqueries Questions

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.

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 define sessions per canonical user ID, not per device, because the requirement says the same user can act from multiple devices. First dedupe duplicate events, then sort by event timestamp, and start a new session when the gap from the previous event is more than 30 minutes.

WITH deduped AS (
  SELECT *
  FROM (
    SELECT
      e.*,
      ROW_NUMBER() OVER (
        PARTITION BY event_id
        ORDER BY ingest_ts DESC
      ) AS rn
    FROM clickstream e
  ) x
  WHERE rn = 1
), ordered AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN LAG(event_ts) OVER (
        PARTITION BY user_id
        ORDER BY event_ts
      ) IS NULL
      THEN 1
      WHEN event_ts - LAG(event_ts) OVER (
        PARTITION BY user_id
        ORDER BY event_ts
      ) > INTERVAL '30 minutes'
      THEN 1
      ELSE 0
    END AS new_session
  FROM deduped
)
SELECT
  user_id,
  event_ts,
  SUM(new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
  ) AS session_id
FROM ordered;

Session metrics
Group by user_id and session_id to get event count, start time, end time, and duration.

Guardrails
Out-of-order events are handled by sorting on event_ts. Duplicates are removed first so they do not create fake activity or split a session.

Approach
I would use a recursive CTE. A recursive CTE is a query that repeatedly joins a result back to the base table, which is a standard way to walk a tree. I would start from each category, climb to its parent, and carry a path string plus a cycle check so bad data cannot recurse forever.

WITH RECURSIVE ancestry AS (
  SELECT
    c.category_id,
    c.category_id AS ancestor_id,
    c.parent_id,
    0 AS depth,
    CAST('>' || c.category_id || '>' AS text) AS path
  FROM categories c

  UNION ALL

  SELECT
    a.category_id,
    p.category_id AS ancestor_id,
    p.parent_id,
    a.depth + 1,
    a.path || p.category_id || '>'
  FROM ancestry a
  JOIN categories p
    ON p.category_id = a.parent_id
  WHERE POSITION('>' || p.category_id || '>' IN a.path) = 0
)
SELECT *
FROM ancestry;

How I would use it

• Build the full ancestor path by grouping the rows for each category_id.
• Roll sales up by joining facts to category_id, then summing again by ancestor_id.
• Exclude or quarantine orphans, where a parent is missing.
• Add a max-depth guard if the warehouse supports it.

Worked example
For Shoes -> Apparel -> Root, the CTE emits three rows with depths 0, 1, 2. That gives a safe path and lets sales from Shoes roll up to Apparel and Root without double counting.

One deterministic row per customer
Use ROW_NUMBER(), which assigns 1 to a single row after you specify a full tie-break order. ROW_NUMBER() is a window function, meaning it ranks rows inside each customer group without collapsing them.

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC, order_id DESC
    ) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

The timestamp decides recency, and order_id DESC makes ties deterministic.

If business later wants every row tied for the latest timestamp
Switch to RANK() and filter on 1.

WITH ranked AS (
  SELECT
    o.*,
    RANK() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC
    ) AS rnk
  FROM orders o
)
SELECT *
FROM ranked
WHERE rnk = 1;

Example
If customer 7 has rows at 10:00, 10:00, and 09:30, the first query returns one row. The second returns both 10:00 rows.