SQL Lab — Time-Series Analysis (Hard)

🖥️

SQL Terminal

📚

Reference

📌 OBJECTIVES — NO HINTS

○Detect gaps in time-series (missing intervals)

○Calculate rolling 24hr average (window function)

○Flag anomalies: readings > mean + 3×stddev

○Build daily aggregation with FLOOR/DATE_FORMAT

○Linear regression for 7-day forecast (manual)

○Create summary materialised table

○Validate: forecast query under 500ms

0 / 7 complete

SQL TERMINAL — TIME-SERIES ANALYSIS (HARD)

MySQL 8.0 — Lab environment loaded Time-Series Analysis (Hard) — Hard tier · No syntax hints provided ⚠ This lab tests independent problem-solving. Reference window available. ──────────────────────────────

mysql>

COMMAND REFERENCE

AVAILABLE COMMANDS

SELECT meter_id, recorded_at, LAG(recorded_at) OVER (PARTITION BY meter_id ORDER BY recorded_at) AS prev FROM readings;
SELECT meter_id, recorded_at, reading_kwh, AVG(reading_kwh) OVER (PARTITION BY meter_id ORDER BY recorded_at ROWS 96 PRECEDING) AS rolling_24h FROM readings;
SELECT meter_id, reading_kwh FROM readings WHERE reading_kwh > (SELECT AVG(reading_kwh)+3*STDDEV(reading_kwh) FROM readings);
SELECT DATE_FORMAT(recorded_at,'%Y-%m-%d') AS day, SUM(reading_kwh) FROM readings GROUP BY day;

Type keywords from the command list. The terminal will recognise your intent even if the exact syntax differs.

MISSION BRIEFING

TIME-SERIES — APEX ENERGY GRID · HARD ⭐⭐⭐

SCENARIO

Apex Energy monitors 50,000 smart meters, each reporting a reading every 15 minutes — 1.2 billion rows and growing. The operations team needs gap detection (missing readings), anomaly detection (readings 3σ above mean), rolling 24-hour averages, and a capacity forecasting query using linear regression in SQL.

OBJECTIVES

Detect gaps in time-series (missing intervals)
Calculate rolling 24hr average (window function)
Flag anomalies: readings > mean + 3×stddev
Build daily aggregation with FLOOR/DATE_FORMAT
Linear regression for 7-day forecast (manual)
Create summary materialised table
Validate: forecast query under 500ms

HARD MODE RULES

No step-by-step guidance. Use the Reference window for command syntax only. You must determine the approach, the correct columns and order, and verify your own results.

Display Mode