SQL Functions and Aggregations | Dataford Interview Questions

Tell me about your experience using SQL functions like DISTINCT, aggregate functions, and TOP, including how you apply them in business analysis queries.

Using PostgreSQL, demonstrate this by analyzing completed orders. Return the top three customer segments by total order value, while counting unique customers. PostgreSQL uses LIMIT rather than TOP.

Output

One row per qualifying customer segment with segment, distinct_customers, and total_order_value.
Include only segments with at least one completed order.
Sort by total order value descending, then unique customers descending, then segment ascending, and return three rows.

Schema

customers

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

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
status	VARCHAR(20)	Order lifecycle status
order_value	NUMERIC(12,2)	Monetary value of the order

Your question is SQL Functions and Aggregations. 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