Nsryjdtyk

I have 2 tables, timeseries and orders

timeseries

+------------+

|  datetime  |

+------------+

| 2018-11-01 |

| 2018-11-02 |

| 2018-11-03 |

+------------+

orders

+------------+-------------+----------+

|  datetime  | customer_id | order_id |

+------------+-------------+----------+

| 2018-11-01 |           1 |        1 |

| 2018-11-02 |           1 |        2 |

| 2018-11-03 |           2 |        3 |

+------------+-------------+----------+

I would like to get number of orders for each customer in each day.

Expected results:

+------------+-------------+--------------+

|  datetime  | customer_id | number_order |

+------------+-------------+--------------+

| 2018-11-01 |           1 |            1 |

| 2018-11-02 |           1 |            1 |

| 2018-11-03 |           1 |            0 |

| 2018-11-01 |           2 |            0 |

| 2018-11-02 |           2 |            0 |

| 2018-11-03 |           2 |            1 |

+------------+-------------+--------------+

I tried LEFT JOIN, but it doesn't return all time series for all customer

SELECT datetime, customer_id, COUNT(order_id) as number_order 

FROM timeseries

LEFT JOIN orders

ON timeseries.datetime = orders.datetime

GROUP BY datetime, customer_id

ORDER BY datetime, customer_id



>> Result



+------------+-------------+--------------+

|  datetime  | customer_id | number_order |

+------------+-------------+--------------+

| 2018-11-01 |           1 |            1 |

| 2018-11-02 |           1 |            1 |

| 2018-11-03 |           2 |            1 |

+------------+-------------+--------------+

I understand left join only ensures returning all rows in table timeseries, but what I need is all rows in table timeseries with each customer_id.

Thank for your help!

You need to cross join all dates and all customers to get all possibile combinations of date and customer ids. Then left join with orders:

SELECT timeseries.datetime, customers.customer_id, COUNT(orders.order_id) as number_order

FROM timeseries

CROSS JOIN (SELECT DISTINCT customer_id FROM orders) AS customers

LEFT JOIN orders ON orders.datetime = timeseries.datetime AND orders.customer_id = customers.customer_id

GROUP BY timeseries.datetime, customers.customer_id

ORDER BY timeseries.datetime, customers.customer_id

use union all then left join

select t1.datetime,customer_id,COUNT(order_id) as number_order from 



(select datetime from  timeseries

union all

select datetime from orders

) t1 left join 

    orders  on t1.datetime=orders.datetime

group by  t1.datetime,customer_id