SQL for Actionable Insights

HardSQL · PostgreSQL

Problem

Explain how you would use SQL to extract actionable insights from a messy, multi-source dataset.

Use the provided customer, sales order, and support ticket tables. Consider inconsistent status values, missing amounts, unmatched source records, inactive customers, and customers with no activity.

Output

One row per active customer.
Include customer_id, customer_name, region, total_revenue, completed_order_count, active_ticket_count, high_priority_active_tickets, last_order_date, and action_status.
Include only orders from 2025-01-01 through 2025-03-31, include customers without matching orders or tickets, and sort by total_revenue descending, then customer_id ascending.

Schema

crm_customers

Column	Type	Description
customer_idPK	INT	Canonical customer identifier
customer_name	VARCHAR(100)	Customer display name
region	VARCHAR(50)	Customer sales region
email	VARCHAR(255)	Customer email address
active	BOOLEAN	Whether the customer is currently active

sales_orders

Column	Type	Description
order_idPK	INT	Sales order identifier
customer_id	INT	Customer identifier from the sales source
order_date	DATE	Date the order was recorded
amount	NUMERIC(12,2)	Order amount
order_status	VARCHAR(30)	Raw order status from the sales source

support_tickets

Column	Type	Description
ticket_idPK	INT	Support ticket identifier
customer_id	INT	Customer identifier from the support source
opened_at	DATE	Date the ticket was opened
priority	VARCHAR(20)	Raw support priority
status	VARCHAR(20)	Raw support ticket status

Tablescrm_customerssales_orderssupport_tickets