SQL Join With Date Filter | Dataford Interview Questions

Your question is SQL Join With Date Filter. Start with the requirements and the three 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

SG Analytics needs a transaction extract for validating activity reported through its analytics dashboards. Write a PostgreSQL query that joins orders to customers and products, returning transactions from February 1, 2024 through February 29, 2024.

Requirements

Use orders as the primary table and retain orders even when the customer or product has no matching record.
Return the order ID, order date, customer details, product details, quantity, unit price, and calculated line total.
Label each row as positive, zero, or negative based on the calculated line total.
Sort the results by order date ascending and order ID ascending.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Reference to the customer placing the order
product_id	INT	Reference to the ordered product
order_date	DATE	Date when the order was placed
quantity	INT	Number of units ordered

customers

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

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category
unit_price	NUMERIC(10,2)	Price for one unit