Window Functions for Recurring Failures | Dataford Interview Questions

Jio operations teams need to identify recurring failures across JioFiber, JioAirFiber, and other Jio service surfaces. Write a PostgreSQL query using a CTE and a window function to find failure patterns that occurred at least twice during January 2026.

Requirements

Join operational logs to the Jio service catalog.
Consider only rows with status = 'FAILED' and timestamps from 2026-01-01 through 2026-01-31.
Use ROW_NUMBER() to sequence failures within each product, surface, failure code, and region.
Return only recurring patterns, including the failure count, first occurrence, and latest occurrence. Sort by failure count descending, then product and failure code.

Schema

jio_operational_logs

Column	Type	Description
log_idPK	INT	Unique operational log identifier
service_id	INT	References the Jio service catalog
surface	VARCHAR(80)	Operational surface where the event occurred
failure_code	VARCHAR(50)	Failure classification code
occurred_at	TIMESTAMP	Timestamp when the event occurred
status	VARCHAR(20)	Operational event status
region	VARCHAR(50)	Geographic operating region

jio_service_catalog

Column	Type	Description
service_idPK	INT	Unique Jio service identifier
product_name	VARCHAR(80)	Jio product or platform name
owner_team	VARCHAR(80)	Team responsible for the service

Your question is Window Functions for Recurring Failures. Start with the requirements and the two tables in the Question tab.

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

Problem