PostgreSQL's REGR_* function family completes simple linear regression using SQL. It models the relationship between response variable Y and explanatory variable X as Y = slope × X + intercept.

REGR_SLOPE(Y, X)      -- Slope: Σ(xi−x̄)(yi−ȳ) / Σ(xi−x̄)²
REGR_INTERCEPT(Y, X)  -- Y-intercept: ȳ − slope × x̄
REGR_R2(Y, X)         -- Coefficient of determination R²: CORR(Y,X)² — shows model fit on a 0–1 scale
REGR_COUNT(Y, X)      -- Number of valid samples excluding NULL

-- Calculate a predicted value (reuse slope and intercept from the CTE):
(slope * 700 + intercept)::numeric

Using the same ad_spend table as Q6, calculate the slope, intercept, coefficient of determination (r_squared), and predicted revenue (predicted_700) when ad_cost = 700 in one row. Round slope to 4 decimal places, intercept to 2 decimal places, r_squared to 4 decimal places, and predicted_700 to the nearest integer.

Expected output (1 row):

slope=446,000÷175,000=2.5486 / intercept=950−2.5486×350=58.00 / R²=0.9914²=0.9829 / prediction=2.5486×700+58.00=1842.02≈1842

WITH stats AS (
  -- Calculate regression parameters together with REGR_* functions (reuse slope and intercept in the CTE)
  SELECT
    REGR_SLOPE(revenue, ad_cost)     AS slope,
    REGR_INTERCEPT(revenue, ad_cost) AS intercept,
    REGR_R2(revenue, ad_cost)        AS r_squared
  FROM ad_spend
)
SELECT
  ROUND(slope::numeric,                     4) AS slope,
  ROUND(intercept::numeric,                 2) AS intercept,
  ROUND(r_squared::numeric,                 4) AS r_squared,
  ROUND((slope * 700 + intercept)::numeric, 0) AS predicted_700  -- predicted value for ad_cost=700 from the regression equation
FROM stats;

/*
  Logical SQL evaluation order:
  1. CTE stats — FROM ad_spend  → read 6 rows
  2. REGR_SLOPE(...)            → calculate the regression slope
  3. REGR_INTERCEPT(...)        → calculate the intercept
  4. REGR_R2(...)               → calculate the coefficient of determination
  5. FROM stats                 → reference the 1-row CTE result
  6. predicted_700              → calculate the predicted value
  7. Apply each ROUND             → round the results
  8. SELECT                     → output 1 row
  */

There are three common types of ranking with window functions, and their behavior differs when values are tied. PARTITION BY assigns independent rankings within each group.

RANK()        OVER (PARTITION BY region ORDER BY sales DESC)
-- Tied rows share a rank / the next rank is skipped: 1,1,3 (2 is skipped)

DENSE_RANK() OVER (PARTITION BY region ORDER BY sales DESC)
-- Tied rows share a rank / the next rank is not skipped: 1,1,2 (consecutive)

ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC)
-- Always consecutive / different numbers even for ties (tie order is nondeterministic): 1,2,3

Using the sales_rep table, assign three descending rankings—RANK, DENSE_RANK, and ROW_NUMBER—within each region based on sales (sales_amount). The output columns should be rep_id, region, sales_amount, rank_val, dense_rank_val, row_num, ordered by region, rank_val, rep_id.

Expected output (9 rows, ascending by region, rank_val, and rep_id):

East/North: two tied rows have rank_val=1, so the next rank is 3 (2 is skipped), while dense_rank_val continues with 2. West has no ties, so all three functions agree. ROW_NUMBER tie order is stabilized with ORDER BY rep_id.

SELECT
  rep_id,
  region,
  sales_amount,
  RANK()        OVER (PARTITION BY region ORDER BY sales_amount DESC) AS rank_val,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS dense_rank_val,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC, rep_id ASC) AS row_num  -- ties stabilized by rep_id
FROM   sales_rep
ORDER BY region, rank_val, rep_id;

/*
  Logical SQL evaluation order:
  1. FROM sales_rep                     → read 9 rows
  2. PARTITION BY region                → split into 3 groups
  3. ORDER BY sales_amount DESC         → sort within each group
  4. RANK()                             → ties share a rank; the next rank is skipped
  5. DENSE_RANK()                       → ties share a rank; no ranks are skipped
  6. ROW_NUMBER()                       → assign consecutive numbers
  7. ORDER BY region, rank_val, rep_id  → apply the final sort
  8. SELECT                             → output 9 rows
  */

NTILE(n) is a window function that divides rows into n groups and assigns each row a bucket number (1–n). It is useful for RFM analysis, user segmentation, and percentile grouping.

NTILE(4) OVER (ORDER BY total_spent DESC)
-- Divide 8 rows into 4 groups → 2 rows per group
-- quartile=1: top 25% by spending (VIP)
-- quartile=4: bottom 25% by spending (light users)
-- When the row count is not divisible by n:
--   add one extra row at a time starting with the upper buckets (e.g., 9 rows ÷ 4 → only bucket 1 has 3 rows)

Divide total_spent in the user_spend table into four groups in descending order, and assign each user a quantile number (quartile: 1–4) and segment name (segment: VIP / Heavy / Middle / Light). Assign the buckets in a CTE, generate the segment name in the outer query, and return the rows ordered by quartile, total_spent DESC.

Expected output (8 rows, ordered by quartile and total_spent DESC):

8 rows ÷ 4 = 2 rows per group. In descending order, the top 2 rows are quartile=1 (VIP), the next 2 are quartile=2 (Heavy), and so on.

WITH segmented AS (
  -- Divide spending into 4 descending buckets with NTILE(4) (1 = top 25%)
  SELECT
    user_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
  FROM user_spend
)
SELECT
  user_id,
  total_spent,
  quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Heavy'
    WHEN 3 THEN 'Middle'
    WHEN 4 THEN 'Light'
  END AS segment
FROM   segmented
ORDER BY quartile, total_spent DESC;

/*
  Logical SQL evaluation order:
  1. CTE segmented — FROM user_spend      → read 8 rows
  2. NTILE(4) OVER (...)                  → assign the 4 quartiles
  3. Outer SELECT FROM segmented          → reference the CTE
  4. CASE quartile                        → convert the number to a business label
  5. ORDER BY quartile, total_spent DESC  → apply the final sort
  6. SELECT                               → output 8 rows
  */

LAG(col, offset, default) is a window function that returns the value from offset rows before the current row. It is essential for time-series comparisons such as month-over-month and year-over-year comparisons, and is simpler to write than a self JOIN.

LAG(col, 1)  OVER (ORDER BY month)  -- Value one row earlier (previous month)
LAG(col, 12) OVER (ORDER BY month)  -- Value twelve rows earlier (same month last year)
LEAD(col, 1) OVER (ORDER BY month)  -- Value one row later (next month)

-- Calculate month-over-month growth (MoM):
-- (current-month revenue − previous-month revenue) / previous-month revenue × 100
-- The first row has no previous month, so it becomes NULL (normal behavior)

Using the monthly_revenue table, calculate each month's revenue, previous-month revenue (prev_revenue), and month-over-month growth rate (mom_growth_pct, to 2 decimal places). First add the previous-month value with LAG in a CTE, then calculate the growth rate in the outer query. It is acceptable for the first month's prev_revenue and mom_growth_pct to be NULL.

Expected output (6 rows, ascending by month):

February: (1,350K−1,200K)÷1,200K×100=12.50 / March: (1,180K−1,350K)÷1,350K×100=−12.59 / April: 28.81 / May: 10.53 / June: −13.69

WITH lagged AS (
  -- Place the previous month's revenue on the current row with LAG (establish it in the CTE for reuse in the outer query)
  SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue)::numeric / prev_revenue * 100,
  2) AS mom_growth_pct  -- NULL prev_revenue automatically propagates NULL
FROM   lagged
ORDER BY month;

/*
  Logical SQL evaluation order:
  1. CTE lagged — FROM monthly_revenue  → read 6 rows
  2. LAG(revenue, 1) OVER (...)         → attach the previous month's revenue
  3. FROM lagged                        → reference the CTE
  4. (revenue - prev) / prev * 100      → calculate the month-over-month growth rate
  5. ROUND(..., 2)                      → round to 2 decimal places
  6. ORDER BY month                     → sort chronologically
  7. SELECT                             → output 6 rows
  */