How to Count the Number of Orders EVERY Day for EVERY Customer with SQL

View All Scripts Login to Run Script

Script Name How to Count the Number of Orders EVERY Day for EVERY Customer with SQL
Description An example of how to answer the question: How many orders did each customer place each day? On days where the customer ordered nothing, show a count of zero. You can do this using a partitioned outer join, as discussed below.
Area SQL General
Contributor Chris Saxon (Oracle)
Created Thursday November 07, 2019

Statement 1
This query returns the dates 1 - 3 Jan 2019. Handy if you don't have a calendar_dates table in your database (though really you should!).
Generate the dates
```
select date'2018-12-31' + level calendar_date   
from   dual  
connect by level <= 3
```
```
CALENDAR_DATE
01-JAN-19
02-JAN-19
03-JAN-19

3 rows selected.
```

CALENDAR_DATE
01-JAN-19
02-JAN-19
03-JAN-19

Statement 2

This finds all the orders placed 1 - 3 Jan 2019.

Find orders placed 1 - 3 January

select * from co.orders o  
where  o.order_datetime >= date '2019-01-01'   
and    o.order_datetime < date '2019-01-04'


ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
1392 01-JAN-19 02.41.19.782784 AM 194 COMPLETE 1
1393 01-JAN-19 07.14.59.876452 AM 355 COMPLETE 7
1394 01-JAN-19 03.29.40.567559 PM 234 COMPLETE 1
1395 01-JAN-19 06.51.54.403137 PM 172 COMPLETE 1
1396 01-JAN-19 08.05.42.227591 PM 232 COMPLETE 1
1397 01-JAN-19 09.01.54.242270 PM 250 COMPLETE 1
1398 01-JAN-19 11.53.03.767038 PM 278 COMPLETE 1
1399 02-JAN-19 02.57.06.263322 AM 238 COMPLETE 1
1400 02-JAN-19 05.48.13.206098 AM 15 COMPLETE 15
1401 02-JAN-19 06.45.26.316789 AM 244 COMPLETE 1
1402 02-JAN-19 07.20.59.009122 AM 246 COMPLETE 1
1403 02-JAN-19 12.08.20.082512 PM 382 COMPLETE 5
1404 02-JAN-19 12.25.35.376616 PM 211 COMPLETE 8
1405 03-JAN-19 02.07.24.120069 AM 315 COMPLETE 1
1406 03-JAN-19 03.03.15.557199 AM 91 COMPLETE 4
1407 03-JAN-19 03.05.21.244600 AM 76 COMPLETE 18
1408 03-JAN-19 09.06.12.113930 AM 292 COMPLETE 2
1409 03-JAN-19 03.45.03.422211 PM 246 COMPLETE 14
1410 03-JAN-19 05.30.38.268584 PM 220 COMPLETE 1
1411 03-JAN-19 06.43.01.384612 PM 41 COMPLETE 1
1412 03-JAN-19 08.11.20.598117 PM 68 COMPLETE 10

21 rows selected.

ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	ORDER_STATUS	STORE_ID
1392	01-JAN-19 02.41.19.782784 AM	194	COMPLETE	1
1393	01-JAN-19 07.14.59.876452 AM	355	COMPLETE	7
1394	01-JAN-19 03.29.40.567559 PM	234	COMPLETE	1
1395	01-JAN-19 06.51.54.403137 PM	172	COMPLETE	1
1396	01-JAN-19 08.05.42.227591 PM	232	COMPLETE	1
1397	01-JAN-19 09.01.54.242270 PM	250	COMPLETE	1
1398	01-JAN-19 11.53.03.767038 PM	278	COMPLETE	1
1399	02-JAN-19 02.57.06.263322 AM	238	COMPLETE	1
1400	02-JAN-19 05.48.13.206098 AM	15	COMPLETE	15
1401	02-JAN-19 06.45.26.316789 AM	244	COMPLETE	1
1402	02-JAN-19 07.20.59.009122 AM	246	COMPLETE	1
1403	02-JAN-19 12.08.20.082512 PM	382	COMPLETE	5
1404	02-JAN-19 12.25.35.376616 PM	211	COMPLETE	8
1405	03-JAN-19 02.07.24.120069 AM	315	COMPLETE	1
1406	03-JAN-19 03.03.15.557199 AM	91	COMPLETE	4
1407	03-JAN-19 03.05.21.244600 AM	76	COMPLETE	18
1408	03-JAN-19 09.06.12.113930 AM	292	COMPLETE	2
1409	03-JAN-19 03.45.03.422211 PM	246	COMPLETE	14
1410	03-JAN-19 05.30.38.268584 PM	220	COMPLETE	1
1411	03-JAN-19 06.43.01.384612 PM	41	COMPLETE	1
1412	03-JAN-19 08.11.20.598117 PM	68	COMPLETE	10

Statement 3

This places the two previous queries in the WITH clause. This creates named subqueries you can use later in your statement like a normal table. The left join returns all rows from calendar_dates. Even if there are no matching rows in ords.

Outer join the orders to the dates

with calendar_dates as (  
  select date'2018-12-31' + level calendar_date   
  from   dual  
  connect by level <= 3  
), ords as (  
  select * from co.orders o  
  where  o.order_datetime >= date '2019-01-01'   
  and    o.order_datetime < date '2019-01-04'   
)  
select calendar_date, customer_id, o.order_datetime  
from   calendar_dates  
left   join ords o  
on    o.order_datetime >= calendar_date  
and   o.order_datetime < calendar_date + 1


CALENDAR_DATE CUSTOMER_ID ORDER_DATETIME
01-JAN-19 194 01-JAN-19 02.41.19.782784 AM
01-JAN-19 355 01-JAN-19 07.14.59.876452 AM
01-JAN-19 234 01-JAN-19 03.29.40.567559 PM
01-JAN-19 172 01-JAN-19 06.51.54.403137 PM
01-JAN-19 232 01-JAN-19 08.05.42.227591 PM
01-JAN-19 250 01-JAN-19 09.01.54.242270 PM
01-JAN-19 278 01-JAN-19 11.53.03.767038 PM
02-JAN-19 238 02-JAN-19 02.57.06.263322 AM
02-JAN-19 15 02-JAN-19 05.48.13.206098 AM
02-JAN-19 244 02-JAN-19 06.45.26.316789 AM
02-JAN-19 246 02-JAN-19 07.20.59.009122 AM
02-JAN-19 382 02-JAN-19 12.08.20.082512 PM
02-JAN-19 211 02-JAN-19 12.25.35.376616 PM
03-JAN-19 315 03-JAN-19 02.07.24.120069 AM
03-JAN-19 91 03-JAN-19 03.03.15.557199 AM
03-JAN-19 76 03-JAN-19 03.05.21.244600 AM
03-JAN-19 292 03-JAN-19 09.06.12.113930 AM
03-JAN-19 246 03-JAN-19 03.45.03.422211 PM
03-JAN-19 220 03-JAN-19 05.30.38.268584 PM
03-JAN-19 41 03-JAN-19 06.43.01.384612 PM
03-JAN-19 68 03-JAN-19 08.11.20.598117 PM

21 rows selected.

CALENDAR_DATE	CUSTOMER_ID	ORDER_DATETIME
01-JAN-19	194	01-JAN-19 02.41.19.782784 AM
01-JAN-19	355	01-JAN-19 07.14.59.876452 AM
01-JAN-19	234	01-JAN-19 03.29.40.567559 PM
01-JAN-19	172	01-JAN-19 06.51.54.403137 PM
01-JAN-19	232	01-JAN-19 08.05.42.227591 PM
01-JAN-19	250	01-JAN-19 09.01.54.242270 PM
01-JAN-19	278	01-JAN-19 11.53.03.767038 PM
02-JAN-19	238	02-JAN-19 02.57.06.263322 AM
02-JAN-19	15	02-JAN-19 05.48.13.206098 AM
02-JAN-19	244	02-JAN-19 06.45.26.316789 AM
02-JAN-19	246	02-JAN-19 07.20.59.009122 AM
02-JAN-19	382	02-JAN-19 12.08.20.082512 PM
02-JAN-19	211	02-JAN-19 12.25.35.376616 PM
03-JAN-19	315	03-JAN-19 02.07.24.120069 AM
03-JAN-19	91	03-JAN-19 03.03.15.557199 AM
03-JAN-19	76	03-JAN-19 03.05.21.244600 AM
03-JAN-19	292	03-JAN-19 09.06.12.113930 AM
03-JAN-19	246	03-JAN-19 03.45.03.422211 PM
03-JAN-19	220	03-JAN-19 05.30.38.268584 PM
03-JAN-19	41	03-JAN-19 06.43.01.384612 PM
03-JAN-19	68	03-JAN-19 08.11.20.598117 PM

Statement 4

The partition by clause on the outer join generates a partition for each customer_id. In effect this is like a cross join between calendar_dates and distinct orders.customer_id. So you get a row for every date and customer. Even if that customer placed no orders on that date.

Partition the join by customer

with calendar_dates as (  
  select date'2018-12-31' + level calendar_date   
  from   dual  
  connect by level <= 3  
), ords as (  
  select * from co.orders o  
  where  o.order_datetime >= date '2019-01-01'   
  and    o.order_datetime < date '2019-01-04'   
)  
select calendar_date, customer_id, o.order_datetime  
from   calendar_dates  
left   join ords o  
  partition by ( o.customer_id )  
on    o.order_datetime >= calendar_date  
and   o.order_datetime < calendar_date + 1  
order  by customer_id, calendar_date


CALENDAR_DATE CUSTOMER_ID ORDER_DATETIME
01-JAN-19 15  - 
02-JAN-19 15 02-JAN-19 05.48.13.206098 AM
03-JAN-19 15  - 
01-JAN-19 41  - 
02-JAN-19 41  - 
03-JAN-19 41 03-JAN-19 06.43.01.384612 PM
01-JAN-19 68  - 
02-JAN-19 68  - 
03-JAN-19 68 03-JAN-19 08.11.20.598117 PM
01-JAN-19 76  - 
02-JAN-19 76  - 
03-JAN-19 76 03-JAN-19 03.05.21.244600 AM
01-JAN-19 91  - 
02-JAN-19 91  - 
03-JAN-19 91 03-JAN-19 03.03.15.557199 AM
01-JAN-19 172 01-JAN-19 06.51.54.403137 PM
02-JAN-19 172  - 
03-JAN-19 172  - 
01-JAN-19 194 01-JAN-19 02.41.19.782784 AM
02-JAN-19 194  - 
03-JAN-19 194  - 
01-JAN-19 211  - 
02-JAN-19 211 02-JAN-19 12.25.35.376616 PM
03-JAN-19 211  - 
01-JAN-19 220  - 
02-JAN-19 220  - 
03-JAN-19 220 03-JAN-19 05.30.38.268584 PM
01-JAN-19 232 01-JAN-19 08.05.42.227591 PM
02-JAN-19 232  - 
03-JAN-19 232  - 
01-JAN-19 234 01-JAN-19 03.29.40.567559 PM
02-JAN-19 234  - 
03-JAN-19 234  - 
01-JAN-19 238  - 
02-JAN-19 238 02-JAN-19 02.57.06.263322 AM
03-JAN-19 238  - 
01-JAN-19 244  - 
02-JAN-19 244 02-JAN-19 06.45.26.316789 AM
03-JAN-19 244  - 
01-JAN-19 246  - 
02-JAN-19 246 02-JAN-19 07.20.59.009122 AM
03-JAN-19 246 03-JAN-19 03.45.03.422211 PM
01-JAN-19 250 01-JAN-19 09.01.54.242270 PM
02-JAN-19 250  - 
03-JAN-19 250  - 
01-JAN-19 278 01-JAN-19 11.53.03.767038 PM
02-JAN-19 278  - 
03-JAN-19 278  - 
01-JAN-19 292  - 
02-JAN-19 292  - 

Rows 1 - 50. More rows exist.

CALENDAR_DATE	CUSTOMER_ID	ORDER_DATETIME
01-JAN-19	15	-
02-JAN-19	15	02-JAN-19 05.48.13.206098 AM
03-JAN-19	15	-
01-JAN-19	41	-
02-JAN-19	41	-
03-JAN-19	41	03-JAN-19 06.43.01.384612 PM
01-JAN-19	68	-
02-JAN-19	68	-
03-JAN-19	68	03-JAN-19 08.11.20.598117 PM
01-JAN-19	76	-
02-JAN-19	76	-
03-JAN-19	76	03-JAN-19 03.05.21.244600 AM
01-JAN-19	91	-
02-JAN-19	91	-
03-JAN-19	91	03-JAN-19 03.03.15.557199 AM
01-JAN-19	172	01-JAN-19 06.51.54.403137 PM
02-JAN-19	172	-
03-JAN-19	172	-
01-JAN-19	194	01-JAN-19 02.41.19.782784 AM
02-JAN-19	194	-
03-JAN-19	194	-
01-JAN-19	211	-
02-JAN-19	211	02-JAN-19 12.25.35.376616 PM
03-JAN-19	211	-
01-JAN-19	220	-
02-JAN-19	220	-
03-JAN-19	220	03-JAN-19 05.30.38.268584 PM
01-JAN-19	232	01-JAN-19 08.05.42.227591 PM
02-JAN-19	232	-
03-JAN-19	232	-
01-JAN-19	234	01-JAN-19 03.29.40.567559 PM
02-JAN-19	234	-
03-JAN-19	234	-
01-JAN-19	238	-
02-JAN-19	238	02-JAN-19 02.57.06.263322 AM
03-JAN-19	238	-
01-JAN-19	244	-
02-JAN-19	244	02-JAN-19 06.45.26.316789 AM
03-JAN-19	244	-
01-JAN-19	246	-
02-JAN-19	246	02-JAN-19 07.20.59.009122 AM
03-JAN-19	246	03-JAN-19 03.45.03.422211 PM
01-JAN-19	250	01-JAN-19 09.01.54.242270 PM
02-JAN-19	250	-
03-JAN-19	250	-
01-JAN-19	278	01-JAN-19 11.53.03.767038 PM
02-JAN-19	278	-
03-JAN-19	278	-
01-JAN-19	292	-
02-JAN-19	292	-

Statement 5

Add the group by to get the number of orders for each day and customer. Then sort the results. Remember you need to count a column from the orders table. Otherwise you'll get the number of ROWS for each day & customer. Which is always at least one.

Group by customer and date

with calendar_dates as (  
  select date'2018-12-31' + level calendar_date   
  from   dual  
  connect by level <= 3  
), ords as (  
  select * from co.orders o  
  where  o.order_datetime >= date '2019-01-01'   
  and    o.order_datetime < date '2019-01-04'   
)  
select customer_id, calendar_date, count ( o.order_datetime )   
from   calendar_dates  
left   join ords o  
  partition by ( o.customer_id )  
on    o.order_datetime >= calendar_date  
and   o.order_datetime < calendar_date + 1  
group  by calendar_date, customer_id  
order  by customer_id, calendar_date


CUSTOMER_ID CALENDAR_DATE COUNT(O.ORDER_DATETIME)
15 01-JAN-19 0
15 02-JAN-19 1
15 03-JAN-19 0
41 01-JAN-19 0
41 02-JAN-19 0
41 03-JAN-19 1
68 01-JAN-19 0
68 02-JAN-19 0
68 03-JAN-19 1
76 01-JAN-19 0
76 02-JAN-19 0
76 03-JAN-19 1
91 01-JAN-19 0
91 02-JAN-19 0
91 03-JAN-19 1
172 01-JAN-19 1
172 02-JAN-19 0
172 03-JAN-19 0
194 01-JAN-19 1
194 02-JAN-19 0
194 03-JAN-19 0
211 01-JAN-19 0
211 02-JAN-19 1
211 03-JAN-19 0
220 01-JAN-19 0
220 02-JAN-19 0
220 03-JAN-19 1
232 01-JAN-19 1
232 02-JAN-19 0
232 03-JAN-19 0
234 01-JAN-19 1
234 02-JAN-19 0
234 03-JAN-19 0
238 01-JAN-19 0
238 02-JAN-19 1
238 03-JAN-19 0
244 01-JAN-19 0
244 02-JAN-19 1
244 03-JAN-19 0
246 01-JAN-19 0
246 02-JAN-19 1
246 03-JAN-19 1
250 01-JAN-19 1
250 02-JAN-19 0
250 03-JAN-19 0
278 01-JAN-19 1
278 02-JAN-19 0
278 03-JAN-19 0
292 01-JAN-19 0
292 02-JAN-19 0

Rows 1 - 50. More rows exist.

CUSTOMER_ID	CALENDAR_DATE	COUNT(O.ORDER_DATETIME)
15	01-JAN-19	0
15	02-JAN-19	1
15	03-JAN-19	0
41	01-JAN-19	0
41	02-JAN-19	0
41	03-JAN-19	1
68	01-JAN-19	0
68	02-JAN-19	0
68	03-JAN-19	1
76	01-JAN-19	0
76	02-JAN-19	0
76	03-JAN-19	1
91	01-JAN-19	0
91	02-JAN-19	0
91	03-JAN-19	1
172	01-JAN-19	1
172	02-JAN-19	0
172	03-JAN-19	0
194	01-JAN-19	1
194	02-JAN-19	0
194	03-JAN-19	0
211	01-JAN-19	0
211	02-JAN-19	1
211	03-JAN-19	0
220	01-JAN-19	0
220	02-JAN-19	0
220	03-JAN-19	1
232	01-JAN-19	1
232	02-JAN-19	0
232	03-JAN-19	0
234	01-JAN-19	1
234	02-JAN-19	0
234	03-JAN-19	0
238	01-JAN-19	0
238	02-JAN-19	1
238	03-JAN-19	0
244	01-JAN-19	0
244	02-JAN-19	1
244	03-JAN-19	0
246	01-JAN-19	0
246	02-JAN-19	1
246	03-JAN-19	1
250	01-JAN-19	1
250	02-JAN-19	0
250	03-JAN-19	0
278	01-JAN-19	1
278	02-JAN-19	0
278	03-JAN-19	0
292	01-JAN-19	0
292	02-JAN-19	0

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database