SQL Total Orders by Client Type | Dataford Interview Questions

Your question is SQL Total Orders by Client Type. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

PenFed Credit Union's marketing analytics team needs to compare order activity from new and existing clients during January 2025. A client is new if January contains their first recorded order, and existing if they placed an order before January 2025.

Write a PostgreSQL query that calculates the total number of January 2025 orders for each client type.

Requirements

Determine each client's first recorded order date.
Include only orders from 2025-01-01 through 2025-01-31, using an exclusive upper date boundary.
Classify each included order as new or existing with CASE and return both categories, including a zero count if a category has no orders.
Return client_type and total_orders, ordered with new before existing.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique PenFed client identifier
client_name	VARCHAR(100)	Client's display name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
client_id	INT	References clients.client_id
order_date	DATE	Date the order was placed