Troubleshooting Slow Queries | Dataford Interview Questions

If a Clickhouse query is running slow, how would you troubleshoot the issue?

For this PostgreSQL exercise, translate the diagnostic step into a query over the supplied query log tables. Analyze executions from 2026-08-19 onward, retain executions lasting at least 1,000 milliseconds, and report repeated slow query patterns.

Output

One row per query pattern and user
Columns: user_name, query_pattern, execution_count, average_duration_ms, maximum_duration_ms, maximum_rows_scanned
Include patterns with at least two qualifying executions
Sort by average duration descending, then query pattern and user name ascending

Schema

query_logs

Column	Type	Description
query_log_idPK	INT	Unique query execution log identifier
user_id	INT	Identifier of the user who ran the query
query_text	TEXT	SQL text recorded for the execution
duration_ms	INT	Execution duration in milliseconds
rows_scanned	INT	Number of rows scanned during execution
started_at	TIMESTAMP	Execution start timestamp

query_users

Column	Type	Description
user_idPK	INT	Unique query user identifier
user_name	VARCHAR(100)	Display name of the query user

Your question is Troubleshooting Slow Queries. 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