Interview Guides

Clean Duplicate Orders for Reporting | Dataford Interview Questions - Dataford - Ace your Interview

Clean Duplicate Orders for Reporting

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsCase WhenData Wrangling

Also asked at

Problem

Task

BrightCart needs a client-facing order report, but the source data contains duplicate order records and some rows have missing product_id values. Write a PostgreSQL query to produce a cleaned report for January 2024.

Requirements

Deduplicate orders_raw by keeping only the latest row per order_id based on updated_at.
Join the cleaned orders to products using product_id.
If product_id is missing or does not match a product, label the product as 'Unknown Product' and the category as 'Unknown'.
Return one row per cleaned order with: order_id, client_name, order_date, product_name, category, quantity, and line_amount (quantity * unit_price).
Include only orders where order_date is between 2024-01-01 and 2024-01-31, ordered by order_date, then order_id.

Table Definitions

`clients`

column	type	description
client_id	INT	Client identifier
client_name	VARCHAR(100)	Client name

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Price per unit

`orders_raw`

column	type	description
row_id	INT	Raw row identifier
order_id	INT	Business order identifier
client_id	INT	Client placing the order
product_id	INT	Ordered product; may be NULL
order_date	DATE	Order date
quantity	INT	Units ordered
updated_at	TIMESTAMP	Last update time for the raw row

Sample Data

`clients`

client_id	client_name
1	Acme Retail
2	Northwind Stores
3	BlueSky Market

`products`

product_id	product_name	category	unit_price
101	Wireless Mouse	Electronics	25.00
102	USB-C Hub	Electronics	45.00
103	Notebook Set	Office	12.00

`orders_raw`

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
3	5002	2	NULL	2024-01-07	1	2024-01-07 10:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0.00
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00

Schema

clients

Column	Type	Description
client_idPK	INT	Primary key for the client
client_name	VARCHAR(100)	Client company name

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Unit price used to calculate line amount

orders_raw

Column	Type	Description
row_idPK	INT	Primary key for the raw order row
order_id	INT	Business order identifier that may appear multiple times
client_id	INT	References the client placing the order
product_id	INT	References the ordered product; may be NULL or invalid
order_date	DATE	Date the order was placed
quantity	INT	Number of units ordered
updated_at	TIMESTAMP	Timestamp of the latest raw row update

clients

client_id	client_name
3	BlueSky Market
1	Acme Retail
5	Urban Outlet
2	Northwind Stores
4	Summit Goods
6	Harbor Shop
7	Cedar Supply
8	Maple Traders
1	Elm Street Market
2	BlueSky Market
3	Brookside Supply
4	Maple Traders
5	Riverbend Supply
6	Redwood Trading
7	Granite Commerce
8	Metro Bazaar
9	Evergreen Retail
10	Elm Street Market
11	Northwind Stores
12	Riverbend Supply
13	Nimbus Shop
14	Peak Outfitters
15	Lighthouse Goods
16	Silverline Mart
17	Peak Outfitters

products

product_id	product_name	category	unit_price
103	Notebook Set	Office	12.00
101	Wireless Mouse	Electronics	25.00
106	Desk Lamp	Home	30.00
102	USB-C Hub	Electronics	45.00
104	Water Bottle	Outdoors	18.50
105	Standing Desk	Furniture	220.00
107	Monitor Stand	Office	40.00
108	Cable Organizer	Accessories	8.00
1	Desk Pad	Education	130
2	Ergonomic Mouse	Hydration	242
3	Power Strip	Security	153
4	Ergonomic Mouse	Hydration	114
5	Ergonomic Mouse	Audio	135
6	Portable Charger	Hydration	183
7	Noise Cancelling Headphones	Mobile	257
8	Wireless Mouse	Unknown	118
9	Mechanical Keyboard	Furniture	193
10	Webcam	Education	40
11	Travel Mug	Productivity	39
12	HDMI Adapter	Networking	257
13	Cable Organizer	Mobile	56
14	Desk Lamp	Unknown	130
15	LED Strip Light	Fitness	5
16	Notebook Set	Outdoors	132
17	Whiteboard Kit	Desk Setup	137
18	Desk Pad	Home	81
19	Office Chair	Furniture	49

orders_raw

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
10	5008	2	102	2023-12-31	1	2023-12-31 18:00:00
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
7	5005	4	999	2024-01-20	2	2024-01-20 14:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
9	5007	6	106	2024-01-31	0	2024-01-31 16:00:00
6	5004	1	104	2024-02-02	2	2024-02-02 09:15:00
3	5002	2		2024-01-07	1	2024-01-07 10:00:00
11	5009	7	108	2024-01-15	-1	2024-01-15 10:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00
8	5006	5	105	2024-01-25	1	2024-01-25 09:00:00
12	5010	8		2024-01-03	2	2024-01-03 08:00:00
1	92	70	51	2024-01-29	62	2024-02-01 01:04:42
2	82	95	68	2024-01-31	62	2024-01-02 06:23:34
3	27	16	90	2024-02-03	48	2024-01-16 19:06:57
4	65	19	90	2023-12-31	69	2024-02-04 04:09:42
5	19	76	92	2024-01-09	34	2024-01-02 04:21:37
6	91	27	78	2024-01-01	38	2024-01-08 12:53:17
7	98	74	28	2024-01-20	25	2023-12-29 06:18:29
8	62	48	79	2024-01-16	41	2024-01-10 11:28:45
9	25	89	35	2024-02-04	71	2024-02-02 14:01:49
10	60	42	72	2024-02-01	14	2024-01-05 08:40:48
11	99	33	52	2023-12-28	82	2024-01-07 02:58:10
12	74	8	32	2024-01-28	74	2024-01-17 20:59:11
13	88	75	17	2024-01-09	56	2024-01-02 08:48:58
14	4	1	39	2024-01-21	15	2024-01-14 07:59:45
15	66	67	53	2024-01-31	18	2024-01-01 11:56:46
16	48	90	66	2024-02-03	61	2024-01-07 19:58:17
17	77	46	35	2024-01-23	4	2024-01-15 15:50:33

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5010	Maple Traders	2024-01-03	Unknown Product	Unknown	2	0
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00
5009	Cedar Supply	2024-01-15	Cable Organizer	Accessories	-1	-8.00
5005	Summit Goods	2024-01-20	Unknown Product	Unknown	2	0
4	Acme Retail	2024-01-21	Unknown Product	Unknown	15	0
5006	Urban Outlet	2024-01-25	Standing Desk	Furniture	1	220.00
5007	Harbor Shop	2024-01-31	Desk Lamp	Home	0	0.00

Problem

Task

Requirements

Deduplicate orders_raw by keeping only the latest row per order_id based on updated_at.
Join the cleaned orders to products using product_id.
If product_id is missing or does not match a product, label the product as 'Unknown Product' and the category as 'Unknown'.
Return one row per cleaned order with: order_id, client_name, order_date, product_name, category, quantity, and line_amount (quantity * unit_price).
Include only orders where order_date is between 2024-01-01 and 2024-01-31, ordered by order_date, then order_id.

Table Definitions

`clients`

column	type	description
client_id	INT	Client identifier
client_name	VARCHAR(100)	Client name

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Price per unit

`orders_raw`

column	type	description
row_id	INT	Raw row identifier
order_id	INT	Business order identifier
client_id	INT	Client placing the order
product_id	INT	Ordered product; may be NULL
order_date	DATE	Order date
quantity	INT	Units ordered
updated_at	TIMESTAMP	Last update time for the raw row

Sample Data

`clients`

client_id	client_name
1	Acme Retail
2	Northwind Stores
3	BlueSky Market

`products`

product_id	product_name	category	unit_price
101	Wireless Mouse	Electronics	25.00
102	USB-C Hub	Electronics	45.00
103	Notebook Set	Office	12.00

`orders_raw`

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
3	5002	2	NULL	2024-01-07	1	2024-01-07 10:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0.00
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00

Schema

clients

Column	Type	Description
client_idPK	INT	Primary key for the client
client_name	VARCHAR(100)	Client company name

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Unit price used to calculate line amount

orders_raw

Column	Type	Description
row_idPK	INT	Primary key for the raw order row
order_id	INT	Business order identifier that may appear multiple times
client_id	INT	References the client placing the order
product_id	INT	References the ordered product; may be NULL or invalid
order_date	DATE	Date the order was placed
quantity	INT	Number of units ordered
updated_at	TIMESTAMP	Timestamp of the latest raw row update

clients

client_id	client_name
3	BlueSky Market
1	Acme Retail
5	Urban Outlet
2	Northwind Stores
4	Summit Goods
6	Harbor Shop
7	Cedar Supply
8	Maple Traders
1	Elm Street Market
2	BlueSky Market
3	Brookside Supply
4	Maple Traders
5	Riverbend Supply
6	Redwood Trading
7	Granite Commerce
8	Metro Bazaar
9	Evergreen Retail
10	Elm Street Market
11	Northwind Stores
12	Riverbend Supply
13	Nimbus Shop
14	Peak Outfitters
15	Lighthouse Goods
16	Silverline Mart
17	Peak Outfitters

products

product_id	product_name	category	unit_price
103	Notebook Set	Office	12.00
101	Wireless Mouse	Electronics	25.00
106	Desk Lamp	Home	30.00
102	USB-C Hub	Electronics	45.00
104	Water Bottle	Outdoors	18.50
105	Standing Desk	Furniture	220.00
107	Monitor Stand	Office	40.00
108	Cable Organizer	Accessories	8.00
1	Desk Pad	Education	130
2	Ergonomic Mouse	Hydration	242
3	Power Strip	Security	153
4	Ergonomic Mouse	Hydration	114
5	Ergonomic Mouse	Audio	135
6	Portable Charger	Hydration	183
7	Noise Cancelling Headphones	Mobile	257
8	Wireless Mouse	Unknown	118
9	Mechanical Keyboard	Furniture	193
10	Webcam	Education	40
11	Travel Mug	Productivity	39
12	HDMI Adapter	Networking	257
13	Cable Organizer	Mobile	56
14	Desk Lamp	Unknown	130
15	LED Strip Light	Fitness	5
16	Notebook Set	Outdoors	132
17	Whiteboard Kit	Desk Setup	137
18	Desk Pad	Home	81
19	Office Chair	Furniture	49

orders_raw

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
10	5008	2	102	2023-12-31	1	2023-12-31 18:00:00
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
7	5005	4	999	2024-01-20	2	2024-01-20 14:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
9	5007	6	106	2024-01-31	0	2024-01-31 16:00:00
6	5004	1	104	2024-02-02	2	2024-02-02 09:15:00
3	5002	2		2024-01-07	1	2024-01-07 10:00:00
11	5009	7	108	2024-01-15	-1	2024-01-15 10:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00
8	5006	5	105	2024-01-25	1	2024-01-25 09:00:00
12	5010	8		2024-01-03	2	2024-01-03 08:00:00
1	92	70	51	2024-01-29	62	2024-02-01 01:04:42
2	82	95	68	2024-01-31	62	2024-01-02 06:23:34
3	27	16	90	2024-02-03	48	2024-01-16 19:06:57
4	65	19	90	2023-12-31	69	2024-02-04 04:09:42
5	19	76	92	2024-01-09	34	2024-01-02 04:21:37
6	91	27	78	2024-01-01	38	2024-01-08 12:53:17
7	98	74	28	2024-01-20	25	2023-12-29 06:18:29
8	62	48	79	2024-01-16	41	2024-01-10 11:28:45
9	25	89	35	2024-02-04	71	2024-02-02 14:01:49
10	60	42	72	2024-02-01	14	2024-01-05 08:40:48
11	99	33	52	2023-12-28	82	2024-01-07 02:58:10
12	74	8	32	2024-01-28	74	2024-01-17 20:59:11
13	88	75	17	2024-01-09	56	2024-01-02 08:48:58
14	4	1	39	2024-01-21	15	2024-01-14 07:59:45
15	66	67	53	2024-01-31	18	2024-01-01 11:56:46
16	48	90	66	2024-02-03	61	2024-01-07 19:58:17
17	77	46	35	2024-01-23	4	2024-01-15 15:50:33

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5010	Maple Traders	2024-01-03	Unknown Product	Unknown	2	0
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00
5009	Cedar Supply	2024-01-15	Cable Organizer	Accessories	-1	-8.00
5005	Summit Goods	2024-01-20	Unknown Product	Unknown	2	0
4	Acme Retail	2024-01-21	Unknown Product	Unknown	15	0
5006	Urban Outlet	2024-01-25	Standing Desk	Furniture	1	220.00
5007	Harbor Shop	2024-01-31	Desk Lamp	Home	0	0.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Sales Summary for StoreEasy

Monthly Customer Spend and RankMedium Monthly Revenue Trends by CategoryMedium

Next question

Clean Duplicate Orders for Reporting

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsCase WhenData Wrangling

Also asked at

Problem

Task

Requirements

Deduplicate orders_raw by keeping only the latest row per order_id based on updated_at.
Join the cleaned orders to products using product_id.
If product_id is missing or does not match a product, label the product as 'Unknown Product' and the category as 'Unknown'.
Return one row per cleaned order with: order_id, client_name, order_date, product_name, category, quantity, and line_amount (quantity * unit_price).
Include only orders where order_date is between 2024-01-01 and 2024-01-31, ordered by order_date, then order_id.

Table Definitions

`clients`

column	type	description
client_id	INT	Client identifier
client_name	VARCHAR(100)	Client name

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Price per unit

`orders_raw`

column	type	description
row_id	INT	Raw row identifier
order_id	INT	Business order identifier
client_id	INT	Client placing the order
product_id	INT	Ordered product; may be NULL
order_date	DATE	Order date
quantity	INT	Units ordered
updated_at	TIMESTAMP	Last update time for the raw row

Sample Data

`clients`

client_id	client_name
1	Acme Retail
2	Northwind Stores
3	BlueSky Market

`products`

product_id	product_name	category	unit_price
101	Wireless Mouse	Electronics	25.00
102	USB-C Hub	Electronics	45.00
103	Notebook Set	Office	12.00

`orders_raw`

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
3	5002	2	NULL	2024-01-07	1	2024-01-07 10:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0.00
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00

Schema

clients

Column	Type	Description
client_idPK	INT	Primary key for the client
client_name	VARCHAR(100)	Client company name

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Unit price used to calculate line amount

orders_raw

Column	Type	Description
row_idPK	INT	Primary key for the raw order row
order_id	INT	Business order identifier that may appear multiple times
client_id	INT	References the client placing the order
product_id	INT	References the ordered product; may be NULL or invalid
order_date	DATE	Date the order was placed
quantity	INT	Number of units ordered
updated_at	TIMESTAMP	Timestamp of the latest raw row update

clients

client_id	client_name
3	BlueSky Market
1	Acme Retail
5	Urban Outlet
2	Northwind Stores
4	Summit Goods
6	Harbor Shop
7	Cedar Supply
8	Maple Traders
1	Elm Street Market
2	BlueSky Market
3	Brookside Supply
4	Maple Traders
5	Riverbend Supply
6	Redwood Trading
7	Granite Commerce
8	Metro Bazaar
9	Evergreen Retail
10	Elm Street Market
11	Northwind Stores
12	Riverbend Supply
13	Nimbus Shop
14	Peak Outfitters
15	Lighthouse Goods
16	Silverline Mart
17	Peak Outfitters

products

product_id	product_name	category	unit_price
103	Notebook Set	Office	12.00
101	Wireless Mouse	Electronics	25.00
106	Desk Lamp	Home	30.00
102	USB-C Hub	Electronics	45.00
104	Water Bottle	Outdoors	18.50
105	Standing Desk	Furniture	220.00
107	Monitor Stand	Office	40.00
108	Cable Organizer	Accessories	8.00
1	Desk Pad	Education	130
2	Ergonomic Mouse	Hydration	242
3	Power Strip	Security	153
4	Ergonomic Mouse	Hydration	114
5	Ergonomic Mouse	Audio	135
6	Portable Charger	Hydration	183
7	Noise Cancelling Headphones	Mobile	257
8	Wireless Mouse	Unknown	118
9	Mechanical Keyboard	Furniture	193
10	Webcam	Education	40
11	Travel Mug	Productivity	39
12	HDMI Adapter	Networking	257
13	Cable Organizer	Mobile	56
14	Desk Lamp	Unknown	130
15	LED Strip Light	Fitness	5
16	Notebook Set	Outdoors	132
17	Whiteboard Kit	Desk Setup	137
18	Desk Pad	Home	81
19	Office Chair	Furniture	49

orders_raw

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
10	5008	2	102	2023-12-31	1	2023-12-31 18:00:00
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
7	5005	4	999	2024-01-20	2	2024-01-20 14:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
9	5007	6	106	2024-01-31	0	2024-01-31 16:00:00
6	5004	1	104	2024-02-02	2	2024-02-02 09:15:00
3	5002	2		2024-01-07	1	2024-01-07 10:00:00
11	5009	7	108	2024-01-15	-1	2024-01-15 10:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00
8	5006	5	105	2024-01-25	1	2024-01-25 09:00:00
12	5010	8		2024-01-03	2	2024-01-03 08:00:00
1	92	70	51	2024-01-29	62	2024-02-01 01:04:42
2	82	95	68	2024-01-31	62	2024-01-02 06:23:34
3	27	16	90	2024-02-03	48	2024-01-16 19:06:57
4	65	19	90	2023-12-31	69	2024-02-04 04:09:42
5	19	76	92	2024-01-09	34	2024-01-02 04:21:37
6	91	27	78	2024-01-01	38	2024-01-08 12:53:17
7	98	74	28	2024-01-20	25	2023-12-29 06:18:29
8	62	48	79	2024-01-16	41	2024-01-10 11:28:45
9	25	89	35	2024-02-04	71	2024-02-02 14:01:49
10	60	42	72	2024-02-01	14	2024-01-05 08:40:48
11	99	33	52	2023-12-28	82	2024-01-07 02:58:10
12	74	8	32	2024-01-28	74	2024-01-17 20:59:11
13	88	75	17	2024-01-09	56	2024-01-02 08:48:58
14	4	1	39	2024-01-21	15	2024-01-14 07:59:45
15	66	67	53	2024-01-31	18	2024-01-01 11:56:46
16	48	90	66	2024-02-03	61	2024-01-07 19:58:17
17	77	46	35	2024-01-23	4	2024-01-15 15:50:33

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5010	Maple Traders	2024-01-03	Unknown Product	Unknown	2	0
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00
5009	Cedar Supply	2024-01-15	Cable Organizer	Accessories	-1	-8.00
5005	Summit Goods	2024-01-20	Unknown Product	Unknown	2	0
4	Acme Retail	2024-01-21	Unknown Product	Unknown	15	0
5006	Urban Outlet	2024-01-25	Standing Desk	Furniture	1	220.00
5007	Harbor Shop	2024-01-31	Desk Lamp	Home	0	0.00

Problem

Task

Requirements

Deduplicate orders_raw by keeping only the latest row per order_id based on updated_at.
Join the cleaned orders to products using product_id.
If product_id is missing or does not match a product, label the product as 'Unknown Product' and the category as 'Unknown'.
Return one row per cleaned order with: order_id, client_name, order_date, product_name, category, quantity, and line_amount (quantity * unit_price).
Include only orders where order_date is between 2024-01-01 and 2024-01-31, ordered by order_date, then order_id.

Table Definitions

`clients`

column	type	description
client_id	INT	Client identifier
client_name	VARCHAR(100)	Client name

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Price per unit

`orders_raw`

column	type	description
row_id	INT	Raw row identifier
order_id	INT	Business order identifier
client_id	INT	Client placing the order
product_id	INT	Ordered product; may be NULL
order_date	DATE	Order date
quantity	INT	Units ordered
updated_at	TIMESTAMP	Last update time for the raw row

Sample Data

`clients`

client_id	client_name
1	Acme Retail
2	Northwind Stores
3	BlueSky Market

`products`

product_id	product_name	category	unit_price
101	Wireless Mouse	Electronics	25.00
102	USB-C Hub	Electronics	45.00
103	Notebook Set	Office	12.00

`orders_raw`

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
3	5002	2	NULL	2024-01-07	1	2024-01-07 10:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0.00
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00

Schema

clients

Column	Type	Description
client_idPK	INT	Primary key for the client
client_name	VARCHAR(100)	Client company name

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Display name of the product
category	VARCHAR(50)	Product category
unit_price	DECIMAL(10,2)	Unit price used to calculate line amount

orders_raw

Column	Type	Description
row_idPK	INT	Primary key for the raw order row
order_id	INT	Business order identifier that may appear multiple times
client_id	INT	References the client placing the order
product_id	INT	References the ordered product; may be NULL or invalid
order_date	DATE	Date the order was placed
quantity	INT	Number of units ordered
updated_at	TIMESTAMP	Timestamp of the latest raw row update

clients

client_id	client_name
3	BlueSky Market
1	Acme Retail
5	Urban Outlet
2	Northwind Stores
4	Summit Goods
6	Harbor Shop
7	Cedar Supply
8	Maple Traders
1	Elm Street Market
2	BlueSky Market
3	Brookside Supply
4	Maple Traders
5	Riverbend Supply
6	Redwood Trading
7	Granite Commerce
8	Metro Bazaar
9	Evergreen Retail
10	Elm Street Market
11	Northwind Stores
12	Riverbend Supply
13	Nimbus Shop
14	Peak Outfitters
15	Lighthouse Goods
16	Silverline Mart
17	Peak Outfitters

products

product_id	product_name	category	unit_price
103	Notebook Set	Office	12.00
101	Wireless Mouse	Electronics	25.00
106	Desk Lamp	Home	30.00
102	USB-C Hub	Electronics	45.00
104	Water Bottle	Outdoors	18.50
105	Standing Desk	Furniture	220.00
107	Monitor Stand	Office	40.00
108	Cable Organizer	Accessories	8.00
1	Desk Pad	Education	130
2	Ergonomic Mouse	Hydration	242
3	Power Strip	Security	153
4	Ergonomic Mouse	Hydration	114
5	Ergonomic Mouse	Audio	135
6	Portable Charger	Hydration	183
7	Noise Cancelling Headphones	Mobile	257
8	Wireless Mouse	Unknown	118
9	Mechanical Keyboard	Furniture	193
10	Webcam	Education	40
11	Travel Mug	Productivity	39
12	HDMI Adapter	Networking	257
13	Cable Organizer	Mobile	56
14	Desk Lamp	Unknown	130
15	LED Strip Light	Fitness	5
16	Notebook Set	Outdoors	132
17	Whiteboard Kit	Desk Setup	137
18	Desk Pad	Home	81
19	Office Chair	Furniture	49

orders_raw

row_id	order_id	client_id	product_id	order_date	quantity	updated_at
10	5008	2	102	2023-12-31	1	2023-12-31 18:00:00
1	5001	1	101	2024-01-05	2	2024-01-05 09:00:00
4	5003	3	103	2024-01-10	4	2024-01-10 08:30:00
7	5005	4	999	2024-01-20	2	2024-01-20 14:00:00
2	5001	1	101	2024-01-05	3	2024-01-05 11:00:00
9	5007	6	106	2024-01-31	0	2024-01-31 16:00:00
6	5004	1	104	2024-02-02	2	2024-02-02 09:15:00
3	5002	2		2024-01-07	1	2024-01-07 10:00:00
11	5009	7	108	2024-01-15	-1	2024-01-15 10:30:00
5	5003	3	103	2024-01-10	5	2024-01-10 12:00:00
8	5006	5	105	2024-01-25	1	2024-01-25 09:00:00
12	5010	8		2024-01-03	2	2024-01-03 08:00:00
1	92	70	51	2024-01-29	62	2024-02-01 01:04:42
2	82	95	68	2024-01-31	62	2024-01-02 06:23:34
3	27	16	90	2024-02-03	48	2024-01-16 19:06:57
4	65	19	90	2023-12-31	69	2024-02-04 04:09:42
5	19	76	92	2024-01-09	34	2024-01-02 04:21:37
6	91	27	78	2024-01-01	38	2024-01-08 12:53:17
7	98	74	28	2024-01-20	25	2023-12-29 06:18:29
8	62	48	79	2024-01-16	41	2024-01-10 11:28:45
9	25	89	35	2024-02-04	71	2024-02-02 14:01:49
10	60	42	72	2024-02-01	14	2024-01-05 08:40:48
11	99	33	52	2023-12-28	82	2024-01-07 02:58:10
12	74	8	32	2024-01-28	74	2024-01-17 20:59:11
13	88	75	17	2024-01-09	56	2024-01-02 08:48:58
14	4	1	39	2024-01-21	15	2024-01-14 07:59:45
15	66	67	53	2024-01-31	18	2024-01-01 11:56:46
16	48	90	66	2024-02-03	61	2024-01-07 19:58:17
17	77	46	35	2024-01-23	4	2024-01-15 15:50:33

Expected Output

order_id	client_name	order_date	product_name	category	quantity	line_amount
5010	Maple Traders	2024-01-03	Unknown Product	Unknown	2	0
5001	Acme Retail	2024-01-05	Wireless Mouse	Electronics	3	75.00
5002	Northwind Stores	2024-01-07	Unknown Product	Unknown	1	0
5003	BlueSky Market	2024-01-10	Notebook Set	Office	5	60.00
5009	Cedar Supply	2024-01-15	Cable Organizer	Accessories	-1	-8.00
5005	Summit Goods	2024-01-20	Unknown Product	Unknown	2	0
4	Acme Retail	2024-01-21	Unknown Product	Unknown	15	0
5006	Urban Outlet	2024-01-25	Standing Desk	Furniture	1	220.00
5007	Harbor Shop	2024-01-31	Desk Lamp	Home	0	0.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Sales Summary for StoreEasy

Monthly Customer Spend and RankMedium Monthly Revenue Trends by CategoryMedium

Next question