“Top 25% customers” and “cutting spending into quartile-based tiers” are examples of segmentation by equal-sized groups, where NTILE(n) is useful. NTILE divides rows ordered by ORDER BY into n buckets that are as even as possible and assigns each row a bucket number (1–n).

NTILE(4) OVER (ORDER BY spend DESC)  -- split spending into four descending buckets: 1 = top 25%, 4 = bottom 25%
-- When the row count is not evenly divisible, the remainder is distributed one row at a time from the first bucket
--   Example: split 9 rows into 4 → 3,2,2,2 (the first bucket gets one extra row)

From customer_spend, split customers into four quartiles in descending spending order and assign each tier a VIP / Gold / Silver / Bronze label. Return user_id, spend, quartile, segment in descending spend order.

Note: 9 rows split into 4 → the one-row remainder goes to the first bucket. The tier sizes are 3,2,2,2.

Expected output (spend descending):

Only the first quartile (VIP) has three customers; the others have two each. This is the behavior of assigning the remainder to the earlier buckets.

Funnel analysis is the starting point for improving conversion: where do users drop off as they move from “view → cart → purchase”? Count the users who reach each step and calculate the conversion rate (CVR) between steps. CVR has two definitions; placing both in one query provides a richer view.

-- ① Overall CVR: the pass-through rate using the entry step as the denominator
FIRST_VALUE(users) OVER (ORDER BY step_no)   -- fix the first value = the entry count
-- ② Step CVR: the conversion rate using the immediately previous step as the denominator
LAG(users) OVER (ORDER BY step_no)           -- the count at the previous step

From events (behavior logs), calculate the number of unique users reaching each step, along with overall CVR and step CVR. Return step, users, overall_cvr, step_cvr in funnel order (view→cart→purchase), with CVRs as percentages rounded to two decimal places.

Note: view reached = 6 users (U1–U6), cart = 4 users (U1,U2,U4,U6), purchase = 2 users (U1,U4).

Expected output (funnel order):

For purchase, overall CVR = 2/6 = 33.33% (versus the entry step), while step CVR = 2/4 = 50.00% (versus cart). The largest drop-off is between cart and purchase.

Cohort analysis groups users by “when they were acquired (their registration month)” and tracks retention by elapsed month afterward. It enables cross-generation comparisons of acquisition campaigns and product improvements. The two keys are calculating the month offset and expanding it into columns with conditional aggregation (a pivot).

-- month offset = active month − cohort month (difference in whole months)
(EXTRACT(YEAR FROM active_month) - EXTRACT(YEAR FROM cohort_month)) * 12
  + (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))

-- Pivot: place active-user counts for each elapsed month side by side (deduplicated)
COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1)   -- active users one month later

From activity (registration-month cohort × active month), calculate each cohort's initial size and one-month and two-month retention rates. Return cohort_month, cohort_size, ret_m1, ret_m2 in ascending cohort_month order, with rates as percentages rounded to one decimal place.

Note: the 2024-01 cohort = {U1,U2,U3}, and the 2024-02 cohort = {U4,U5}. Each row records that the user was active in that month.

Expected output (cohort_month ascending):

January cohort: 2 of 3 users one month later (66.7%) and 1 of 3 two months later (33.3%). The February cohort retains 100% after one month, which may indicate stronger acquisition quality.

Daily sales and DAU often have no row at all on days without activity. If you feed this data directly into a line chart or moving average (basic Q6), missing days are compressed and both the chart and calculations shift. The solution is to generate a continuous date skeleton (a date spine), left join the actuals onto it, and fill missing days with 0. A recursive CTE is the classic way to generate the skeleton.

WITH RECURSIVE calendar AS (
  SELECT DATE '2024-03-01' AS d        -- ① anchor: one starting row (non-recursive term)
  UNION ALL                              -- ② stack the results vertically
  SELECT d + 1 FROM calendar          -- ③ recursive term: reference itself and add one day
  WHERE d < DATE '2024-03-05'           -- ④ stop condition: stop when the endpoint is reached
)

Turn the sparse daily_sales data into a continuous time series where all five days from 2024-03-01 to 2024-03-05 appear and days without activity have sales 0. Return event_date, sales in ascending date order. Generate the date spine with a recursive CTE and fill it with LEFT JOIN + COALESCE.

Note: 03-02 and 03-05 have no rows (they are missing zero-sales days). The goal is to fill them in.

Expected output (date ascending, all 5 days):

The missing dates 03-02 and 03-05 are filled with 0, making five consecutive days. Moving averages and day-over-day comparisons can now be calculated correctly.