SQL Anomaly Spikes Query

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL Anomaly Spikes Query. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Given a table of high-frequency sensor logs, write a query to identify anomalous spikes using ranking and partitioning functions.

Treat a spike as a reading among the two highest distinct values for its sensor. Exclude readings with missing values.

Output

One row per identified spike, with sensor_id, log_id, recorded_at, reading_value, and spike_rank.
Include ranks 1 and 2 for each sensor, preserving ties at the same rank.
Order by sensor_id, spike_rank, recorded_at, and log_id.

sensor_logs

Column	Type	Description
log_idPK	INT	Unique sensor log identifier
sensor_id	VARCHAR(30)	Sensor that produced the reading
recorded_at	TIMESTAMPTZ	Timestamp when the reading was recorded
reading_value	NUMERIC(10,2)	Measured sensor value

Tablessensor_logs

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results