SQL for Operational Trend Analysis

MediumSQL · PostgreSQL

Problem

Write a SQL query to analyze operational performance by joining transactions, customers, and support tickets, then aggregate by week to identify trends in volume, resolution time, and failure rate.

Use the provided tables and include weeks represented by either transactions or support tickets. Exclude unresolved tickets from average resolution time, but retain their week in the output.

Output

One row per week, with week_start, transaction_volume, ticket_volume, avg_resolution_hours, and failure_rate_pct.
Include weeks with only transactions or only tickets, use zero for missing volumes and failure rates, and sort by week_start ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
segment	VARCHAR(30)	Customer business segment

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_at	TIMESTAMP	Transaction timestamp
status	VARCHAR(20)	Transaction processing status
amount	NUMERIC(12,2)	Transaction amount

support_tickets

Column	Type	Description
ticket_idPK	INT	Unique support ticket identifier
customer_id	INT	Customer associated with the ticket
created_at	TIMESTAMP	Ticket creation timestamp
resolved_at	TIMESTAMP	Ticket resolution timestamp
issue_type	VARCHAR(40)	Support issue category

Tablestransactionscustomerssupport_tickets