Approach (brief): align metric daily values with revenue for the same dates over the last 180 days, compute Pearson correlation per metric using the formula cov(X,Y) / (σX * σY) computed from SUMs. Ensure enough paired observations (filter small n) and handle missing days by joining against a calendar of the 180-day window so absent metric values become NULL (or imputed). For non-linear relationships, inspect scatterplots, use Spearman rank, or apply transformations (log, sqrt) or binning.ANSI SQL example (assumes a calendar table dates(date) with at least the last 180 days):sql
WITH window_dates AS (
SELECT date
FROM dates
WHERE date BETWEEN CURRENT_DATE - INTERVAL '179' DAY AND CURRENT_DATE
),
metric_aligned AS (
SELECT d.date, m.metric_name, m.metric_value
FROM window_dates d
LEFT JOIN daily_metrics m
ON m.date = d.date
),
sales_aligned AS (
SELECT d.date, s.revenue
FROM window_dates d
LEFT JOIN daily_sales s
ON s.date = d.date
),
pairs AS (
SELECT
m.metric_name,
m.date,
m.metric_value AS x,
s.revenue AS y
FROM metric_aligned m
JOIN sales_aligned s ON m.date = s.date
-- This yields NULL x for missing metric days; below we'll only use rows with both values
),
stats AS (
SELECT
metric_name,
COUNT(1) AS n,
SUM(x) AS sum_x,
SUM(y) AS sum_y,
SUM(x*y) AS sum_xy,
SUM(x*x) AS sum_x2,
SUM(y*y) AS sum_y2
FROM pairs
WHERE x IS NOT NULL AND y IS NOT NULL
GROUP BY metric_name
)
SELECT
metric_name,
n,
CASE
WHEN n < 30 THEN NULL -- insufficient data
ELSE ( (sum_xy - (sum_x * sum_y) / n)
/ (SQRT(sum_x2 - (sum_x*sum_x)/n) * SQRT(sum_y2 - (sum_y*sum_y)/n)) )
END AS pearson_corr
FROM stats
ORDER BY ABS(pearson_corr) DESC;
