SQL

These assume a table orders (order_id, order_date, region, channel, amount) and a table months (ad_spend, sales). PostgreSQL, with BigQuery and Snowflake notes where they differ.

Count rows and values

SELECT COUNT(*) AS n_rows,
       COUNT(amount) AS n_amount
FROM orders;

COUNT(amount) skips NULLs, COUNT(*) does not. The gap is your missing values.

Mean

SELECT AVG(amount) AS mean
FROM orders;

AVG skips NULLs. Same in BigQuery and Snowflake.

Median

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders;

Snowflake takes the same code or MEDIAN(amount). BigQuery: PERCENTILE_CONT(amount, 0.5) OVER () is a window function, so wrap it in SELECT DISTINCT.

Quartiles

SELECT PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75])
       WITHIN GROUP (ORDER BY amount) AS quartiles
FROM orders;

Returns Q1, median, Q3 as an array. Same method as QUARTILE.INC in Excel. Snowflake and BigQuery take one fraction per call.

Sample SD and variance

SELECT STDDEV_SAMP(amount) AS sd,
       VAR_SAMP(amount) AS variance
FROM orders;

Both divide by n minus 1. STDDEV and VARIANCE mean the sample versions in all three. Use STDDEV_POP and VAR_POP for a whole population.

Summary by group

SELECT region,
       COUNT(*) AS n,
       AVG(amount) AS mean,
       STDDEV_SAMP(amount) AS sd,
       MIN(amount) AS min,
       MAX(amount) AS max
FROM orders
GROUP BY region
ORDER BY mean DESC;

One row per region. Works as written in BigQuery and Snowflake.

Percent of total

SELECT region,
       SUM(amount) AS total,
       1.0 * SUM(amount) / SUM(SUM(amount)) OVER () AS share
FROM orders
GROUP BY region;

The window runs after GROUP BY, so SUM(SUM(amount)) OVER () is the grand total. Use OVER (PARTITION BY region) with GROUP BY region, channel for each channel's share within its region. The 1.0 stops integer division in PostgreSQL.

Two-way count table

SELECT region,
       COUNT(*) FILTER (WHERE channel = 'web') AS web,
       COUNT(*) FILTER (WHERE channel = 'store') AS store,
       COUNT(*) AS total
FROM orders
GROUP BY region;

Rows are regions, columns are channels. BigQuery: COUNTIF(channel = 'web'). Snowflake: COUNT_IF(channel = 'web'). SUM(CASE WHEN channel = 'web' THEN 1 ELSE 0 END) works everywhere.

Correlation

SELECT CORR(ad_spend, sales) AS r
FROM months;

Pearson r. Rows with a NULL in either column are skipped. Same name in BigQuery and Snowflake.

Simple regression

SELECT REGR_SLOPE(sales, ad_spend) AS slope,
       REGR_INTERCEPT(sales, ad_spend) AS intercept,
       REGR_R2(sales, ad_spend) AS r_squared,
       REGR_COUNT(sales, ad_spend) AS n
FROM months;

y first, then x, as in Excel's SLOPE. Snowflake has the same functions. BigQuery has none: slope is COVAR_SAMP(ad_spend, sales) / VAR_SAMP(ad_spend), R squared is POWER(CORR(ad_spend, sales), 2), and intercept is AVG(sales) minus slope times AVG(ad_spend) over rows with both values.

z-score for each row

SELECT order_id, amount,
       (amount - AVG(amount) OVER ())
         / STDDEV_SAMP(amount) OVER () AS z
FROM orders;

Put PARTITION BY region inside each OVER () for a z-score within each region. |z| above 3 is a common outlier flag. Same in BigQuery and Snowflake.

Moving average

WITH daily AS (
  SELECT order_date, SUM(amount) AS sales
  FROM orders
  GROUP BY order_date
)
SELECT order_date, sales,
       AVG(sales) OVER (ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_day
FROM daily
ORDER BY order_date;

ROWS counts rows, so a day with no orders shortens the window; fill missing dates first if that matters. The first six rows average fewer days. Same in BigQuery and Snowflake.

Random sample of rows

SELECT * FROM orders TABLESAMPLE BERNOULLI (10);

SELECT * FROM orders
ORDER BY RANDOM()
LIMIT 500;

BERNOULLI keeps each row with a 10% chance, so the count varies; add REPEATABLE (42) for the same sample each run. ORDER BY RANDOM() returns exactly 500 but sorts the whole table. BigQuery: ORDER BY RAND() LIMIT 500. Snowflake: SAMPLE (10) or SAMPLE (500 ROWS).