SQL

How many orders are there in each status on every calendar date in the time range of the data set (5/3/2017 - 7/1/2017)?

Note: If an order is in a pending status on 6/5 and gets moved to completed on 6/8, it is in the pending status on 6/5, 6/6 and 6/7.

TableName: order_status_history

order_id order_status created_atp

1 pending 2017-05-03 20:39:24

2 pending 2017-05-05 16:14:08

3 pending 2017-05-06 17:51:16

4 pending 2017-05-07 05:46:42

5 pending 2017-05-08 16:15:13

6 pending 2017-05-09 22:06:45

7 pending 2017-05-10 10:57:34

8 pending 2017-05-11 15:57:14

9 pending 2017-05-13 08:43:20

10 pending 2017-05-14 16:59:10

11 pending 2017-05-16 09:20:51

12 pending 2017-05-19 22:48:13

13 pending 2017-05-20 01:48:55

14 pending 2017-05-23 16:48:04

15 pending 2017-05-23 20:22:37

16 pending 2017-05-26 01:53:57

17 pending 2017-05-26 03:10:22

18 pending 2017-05-26 16:44:24

19 pending 2017-05-26 19:55:09

20 pending 2017-05-29 07:01:56

21 pending 2017-06-01 07:33:33

22 pending 2017-06-02 07:41:42

23 pending 2017-06-06 22:45:04

24 pending 2017-06-09 11:36:03

25 pending 2017-06-14 21:19:19

26 pending 2017-06-14 21:53:15

27 pending 2017-06-17 15:58:49

28 pending 2017-06-20 19:59:00

29 pending 2017-06-21 21:59:13

30 pending 2017-06-25 02:47:33

1 shipped 2017-05-07 20:39:24

2 shipped 2017-05-09 16:14:08

3 shipped 2017-05-10 17:51:16

4 shipped 2017-05-11 05:46:42

5 shipped 2017-05-12 16:15:13

6 shipped 2017-05-13 22:06:45

7 shipped 2017-05-14 10:57:34

8 shipped 2017-05-15 15:57:14

9 shipped 2017-05-17 08:43:20

10 shipped 2017-05-18 16:59:10

11 shipped 2017-05-20 09:20:51

12 shipped 2017-05-23 22:48:13

13 shipped 2017-05-24 01:48:55

14 shipped 2017-05-27 16:48:04

15 shipped 2017-05-27 20:22:37

16 shipped 2017-05-30 01:53:57

1 delivered 2017-05-11 20:39:24

2 delivered 2017-05-13 16:14:08

3 delivered 2017-05-14 17:51:16

4 delivered 2017-05-15 05:46:42

5 delivered 2017-05-16 16:15:13