Task overview:You need to design an anomaly-detection exercise for a BI Analyst candidate: dataset schema, short prompt, scoring rubric, and three expected deliverables (SQL snippet, visualization, explanation).Dataset schema (CSV / table: daily_metrics):- date (DATE)- metric_name (VARCHAR) — e.g., "active_users"- metric_value (FLOAT)- org_id (INT)- channel (VARCHAR) — e.g., "web", "mobile"Prompt (short):We track daily "active_users" per org and channel. The metric shows strong weekly seasonality (Mon–Sun pattern). Using the last 90 days for org_id = 42 and channel = 'web', detect any anomalous days in metric_value, explain your method, and produce a visualization and a SQL query that extracts the candidate anomalies. Assume data quality is generally good.Scoring criteria (total 100):- Method & reasoning (30): correct handling of weekly seasonality, clear statistical approach (e.g., day-of-week baseline, rolling z-score, or STL decomposition).- SQL correctness (25): query reproduces candidate's preprocessing and anomaly selection.- Visualization (25): clear time-series with seasonality context (e.g., overlay baseline and shaded anomalies).- Explanation & actionability (20): concise interpretation, potential causes, business impact, and recommended next steps.Expected deliverables (examples):1) SQL snippet (example approach: day-of-week baseline + z-score)sql
WITH filtered AS (
SELECT date, metric_value,
EXTRACT(DOW FROM date) AS dow
FROM daily_metrics
WHERE org_id = 42 AND channel = 'web'
AND metric_name = 'active_users'
AND date >= CURRENT_DATE - INTERVAL '90 days'
),
dow_stats AS (
SELECT dow,
AVG(metric_value) AS mu,
STDDEV_POP(metric_value) AS sigma
FROM filtered
GROUP BY dow
)
SELECT f.date, f.metric_value, f.dow, ds.mu, ds.sigma,
(f.metric_value - ds.mu) / NULLIF(ds.sigma,0) AS z_score
FROM filtered f
JOIN dow_stats ds USING (dow)
WHERE ABS((f.metric_value - ds.mu) / NULLIF(ds.sigma,0)) > 2.5
ORDER BY date;
