Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Days between order and return · SQL scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Days between order and return

The returns policy is 14 days, and nobody knows how many returns come in late. shop_orders.placed_at is a timestamp to the second; shop_returns.returned_on is a plain date. Return one row per matched return with the whole number of days between the two, ordered by return_id.

Result columns · in this order

`return_id`	The return.
`order_id`	The order it was returned against.
`days_to_return`	Whole days between the order and the return.

How to approach it

Reduce both values to a date before subtracting, then convert the difference to a whole number.

Sample input

shop_ordersOne row per online order.

order_id	customer_id	placed_at	status	order_total
1001	c1	2026-01-05 10:00:00	paid	120
1002	c2	2026-01-12 14:30:00	Paid	80
1003	c1	2026-01-20 09:15:00	pending	45
1004	c3	2026-01-31 23:30:00	PAID	200
1005	c2	2026-02-01 00:15:00	paid	60
1006	c4	2026-02-03 11:00:00	paid	150
1007	c3	2026-02-10 16:45:00	refunded	90
1008	c1	2026-02-14 12:00:00	paid	300
1009	c5	2026-02-18 08:30:00	Paid	75
1010	c4	2026-02-25 19:20:00	pending	85
1011	c5	2026-02-28 21:00:00	paid	130
1012	c2	2026-03-02 10:10:00	paid	95

12 rows — scroll inside the table to see them all.

shop_returnsOne row per returned item. order_id is NULL when the return was never matched.

return_id	order_id	reason	refund_amount	returned_on
1	1001	damaged	60	2026-01-09
2	1004	wrong size	50	2026-02-05
3	1004	wrong size	50	2026-02-20
4	1007	changed mind	90	2026-03-01
5	null	unlinked	25	2026-02-11
6	1008	damaged	150	2026-02-16
7	1011	late delivery	130	2026-03-20
8	1002	damaged	80	2026-01-15
9	1002	damaged	80	2026-01-18
10	1008	damaged	150	2026-02-20

10 rows — all rows shown.

Expected output

The exact result your query should return for this input.

return_id	order_id	days_to_return
1	1001	4
2	1004	5
3	1004	20
4	1007	19
6	1008	2
7	1011	20
8	1002	3
9	1002	6
10	1008	6

9 rows — all rows shown.

Constraints

1days_to_return is a whole number of calendar days, not a fraction.
2Compare like with like — the times of day in placed_at would otherwise leak into the difference.
3A return whose order_id is NULL has no order to measure against and does not appear.
4This sandbox is SQLite, so date arithmetic uses julianday. Name the dialect when you write date maths in an interview.

Worked example

Return 1 is against order 1001, placed 2026-01-05 10:00:00 and returned 2026-01-09. The answer is 4 days. Subtracting the raw values would compare a date to a timestamp and mix in the 10:00 — giving 3.58 rather than 4. Trimming placed_at to its date part first makes both sides the same kind of thing.

What this tests

Date arithmetic across two different value shapes, and knowing that date functions are dialect-specific rather than standard SQL.

SQL

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules