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.

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 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.

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 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.