SQL complex joins in production data | Dataford Interview Questions

Describe your experience with SQL queries—how do you handle complex joins when analyzing production data for Citi?

Write a PostgreSQL query using the provided Citi production query catalog, run history, and data source metadata. Return production queries from January 2025 that ran at least twice and had at least one failure.

Output

One row per qualifying production query with query_id, query_name, source_name, total_runs, failed_runs, avg_success_duration_seconds, and latest_status.
Use Unknown source when source metadata is unavailable. Sort by failed_runs descending, then query_id ascending.

Schema

citi_query_catalog

Column	Type	Description
query_idPK	INT	Unique identifier for a production query
query_name	VARCHAR(100)	Catalog name of the query
environment	VARCHAR(20)	Execution environment for the query
source_id	INT	Optional reference to the source system

citi_query_runs

Column	Type	Description
run_idPK	INT	Unique identifier for a query execution
query_id	INT	Query executed by the run
started_at	TIMESTAMP	Run start timestamp
status	VARCHAR(20)	Execution status
duration_seconds	INT	Execution duration in seconds

citi_data_sources

Column	Type	Description
source_idPK	INT	Unique source system identifier
source_name	VARCHAR(100)	Name of the source system
environment	VARCHAR(20)	Environment associated with the source

Your question is SQL complex joins in production data. Start with the requirements and the three 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