A percentile is a statistic expressed as a position when data is sorted in ascending order. The median (p50) is widely used in practice as a robust representative value that is not strongly affected by outliers. PostgreSQL provides two kinds: PERCENTILE_CONT (continuous interpolation) and PERCENTILE_DISC (nearest discrete value).

-- Continuous interpolation: linearly interpolate between adjacent values (may be a decimal)
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)

-- Discrete value: return the nearest value that actually exists (always an original data value)
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount)

From the amount column of the orders table, calculate the average, median, third quartile (p75), and top-10% threshold (p90) in one row. Return avg_amount, median_amount, p75_amount, p90_amount (round avg_amount to an integer).

Expected output (one row):

Ascending order: [800, 1200, 1800, 2400, 3500, 5200] — Median: 1800+(2400-1800)×0.5=2100 / The gap of 383 between avg=2483 and the median shows the effect of the outlier (5200).

SELECT
  ROUND(AVG(amount), 0)                                           AS avg_amount,
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY amount)           AS median_amount,  -- Median
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount)           AS p75_amount,     -- Third quartile
  PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY amount)           AS p90_amount      -- Top-10% threshold
FROM orders;

/*
  Execution order (logical SQL evaluation order):
  1. FROM orders            → Read 6 rows
  2. AVG(amount)            → Calculate the average
  3. PERCENTILE_CONT(0.5)   → Interpolate the median
  4. PERCENTILE_CONT(0.75)  → Interpolate the third quartile
  5. PERCENTILE_CONT(0.90)  → Interpolate the 90th percentile
  6. SELECT                 → Output one row
  */

Standard deviation is a statistic that measures the size of variation around the mean. Even when two means are identical, a large standard deviation indicates polarized ratings, while a small one indicates stable ratings.

STDDEV(col)      -- Sample standard deviation (divide by n-1, SQL default)
STDDEV_POP(col)  -- Population standard deviation (divide by n)
VARIANCE(col)    -- Sample variance (the square of standard deviation)

From the product_ratings table, calculate the rating count, average rating, and standard deviation for each product. Return product_id, rating_count, avg_rating, stddev_rating (round each to two decimal places), ordered by product_id ascending.

Expected output (product_id ascending):

A: ratings {4,5,4,5,4}→stable high ratings (stddev=0.55) / B: ratings {1,5,3,5,1}→the mean is 3.0, but extreme ratings are mixed in (stddev=2.0)

SELECT
  product_id,
  COUNT(*)                                 AS rating_count,
  ROUND(AVG(rating)::numeric, 2)           AS avg_rating,    -- Cast to numeric before ROUND
  ROUND(STDDEV(rating)::numeric, 2)        AS stddev_rating  -- Sample standard deviation (divide by n-1)
FROM   product_ratings
GROUP BY product_id
ORDER BY product_id;

/*
  Execution order (logical SQL evaluation order):
  1. FROM product_ratings    → Read 10 rows
  2. GROUP BY product_id     → Split into 2 groups
  3. COUNT/AVG/STDDEV        → Evaluate aggregates (sample standard deviation)
  4. ROUND(...::numeric, 2)  → Round to two decimal places
  5. ORDER BY product_id     → Sort ascending
  */

A histogram visualizes the shape of a distribution by dividing continuous data into equal-width intervals (buckets) and counting the rows in each interval. PostgreSQL's WIDTH_BUCKET function dynamically creates buckets without writing many CASE WHEN branches.

WIDTH_BUCKET(value, lo, hi, count)
-- value: the value to classify
-- lo:    lower bound (start of bucket 1)
-- hi:    upper bound (the out-of-range boundary)
-- count: number of buckets
-- return: bucket number (1 to count) / out of range is 0 or count+1

Example: WIDTH_BUCKET(1800, 0, 6000, 3)
→ Bucket width = 6000/3 = 2000 → [0,2000) [2000,4000) [4000,6000)
→ 1800 is in [0,2000), so bucket=1

Divide the amount column of the orders table into three buckets 2,000 yen wide (0 to 5,999 yen), then calculate the bucket number, amount range, and count. Assign bucket numbers in a CTE and aggregate in the outer query. Return bucket, bucket_range, order_count ordered by bucket ascending.

Expected output (bucket ascending):

Bucket 1: 800,1200,1800 / Bucket 2: 2400,3500 / Bucket 3: 5200 — Orders are concentrated in the low-to-middle price range.

WITH bucketed AS (
  -- Assign a bucket number to each order (6000 yen split into 3: width 2000 yen/bucket)
  SELECT
    WIDTH_BUCKET(amount, 0, 6000, 3)  AS bucket,
    amount
  FROM orders
)
SELECT
  bucket,
  ((bucket - 1) * 2000)::text || 'to' ||
    (bucket * 2000 - 1)::text    AS bucket_range,  -- Dynamically generate the label
  COUNT(*)                        AS order_count
FROM   bucketed
GROUP BY bucket
ORDER BY bucket;

/*
  Execution order (logical SQL evaluation order):
  1. CTE bucketed
  2. Outer query
  3. ORDER BY bucket  → Sort by bucket number ascending
  */

A moving average is a statistical technique for removing short-term noise from daily data and understanding the trend. PostgreSQL's window function AVG() OVER (... ROWS BETWEEN) calculates a moving average for every row in one query.

AVG(col) OVER (
  ORDER BY date_col
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW   -- Previous 2 rows + current row = 3-day moving average
)

From the daily_sales table, calculate daily revenue and a three-day moving average (ma3). ma3 is the average of “today + the previous two days,” rounded to an integer. Return sale_date, revenue, ma3 ordered by sale_date ascending.

Expected output (sale_date ascending):

Day 1: one row only→1000 / Day 2: (1000+1200)/2=1100 / Day 3 onward: average of the previous two days plus the current day / ma3 smooths revenue noise and shows the trend.

SELECT
  sale_date,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Latest 3 days (including the current day)
    ), 0
  ) AS ma3
FROM   daily_sales
ORDER BY sale_date;

/*
  Execution order (logical SQL evaluation order):
  1. FROM daily_sales         → Read 7 rows
  2. ORDER BY sale_date       → Fix row order by ascending date
  3. AVG(revenue) OVER (...)  → Moving average of current row plus previous 2
  4. ROUND(..., 0)            → Round to an integer
  5. SELECT + ORDER BY        → Output in ascending date order
  */