SQL for Sales and Logs | Dataford Interview Questions

Your question is SQL for Sales and Logs. 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

Shopify wants a monthly view of merchant sales and admin engagement. Write a PostgreSQL query that combines Shopify sales records with admin activity logs without allowing multiple log rows to duplicate sales.

Requirements

Include each merchant and month with at least one completed sale during Q1 2025.
Return completed gross sales, completed order count, and distinct admin login sessions.
Preserve merchants with no matching login activity by returning zero sessions and label them Not engaged; label merchants with at least one session Engaged.
Sort results chronologically by month, then by merchant ID.

Schema

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
merchant_id	INT	Shopify merchant identifier
occurred_at	TIMESTAMP	Timestamp when the sale occurred
amount	DECIMAL(12,2)	Sale amount in the merchant billing currency
status	VARCHAR(20)	Sale processing status

merchant_logs

Column	Type	Description
log_idPK	INT	Unique log identifier
merchant_id	INT	Shopify merchant identifier
logged_at	TIMESTAMP	Timestamp when the event was logged
event_type	VARCHAR(30)	Type of merchant activity event
session_id	VARCHAR(40)	Browser or application session identifier