SQL查找10月份销售最高的一天--有促销的商店

Question

我是这里的SQL初学者，非常努力地应对这样的挑战：

有一整年的在线活动，我想知道：

1. 在十月份的每一天，一共有多少家商店参与，总共产生了多少订单，总共产生了多少销售。
2. 参加活动的商店的销售额最大的日期是什么，销售额是多少，所有商店的总销售量是多少(参与与否)。

我用的DMBS是Postgres。提前谢谢！

这是我的桌子：

表1- ele_slr_wide (所有存储ids)

shop_id  
A000001
A000002
A000003

表2- ele_slr_ord_wide_di (所有订单)

shop_id  order_id activity_id   pay_time   pay_amt
A000001  001       001         2019-10-25  100
A000001  002       002         2019-10-25  200
A000002  003       002         2019-10-27  200
A000003  004       NA          2019-10-25  100   

表3- ele_slr_act_wide (参与商店)

shop_id  activity_id   start_time  end_time
A000001  001           2019-9-25   2019-10-31
A000001  002           2019-10-5   2019-10-31
A000002  002           2019-10-5   2019-10-31

表4- ele_slr_ord_wide_di (参与商店订单)

shop_id  order_id activity_id   pay_time   pay_amt
A000001  001       001         2019-10-25  100
A000001  002       001         2019-10-25  200
A000002  003       002         2019-10-27  200

预期产出1：

Date      Participated_stores_count  total_orders   total_sales
2019-10-1           xxx                 xxx             xxx
...
2019-10-31          xxx                 xxx             xxx

预期产出2：

Date_max_sales   sales_participated_stores   total_sales
2019-10-15           300                      400

Answer 1

试着在下面

DB小提琴

查询1:

WITH numbers as
(
    SELECT date_trunc('day', dd):: date
    FROM generate_series( '2019-10-01'::timestamp , '2019-10-31'::timestamp, '1 day'::interval) dd
)
SELECT date_trunc,COUNT(DISTINCT E.shop_id) ParticipatedStores, COUNT(DISTINCT ES.order_id) TotalOrders, SUM(ES.pay_amt) total_sales
FROM numbers n LEFT JOIN ele_slr_act_wide E ON   date_trunc BETWEEN E.start_time AND E.end_time
LEFT JOIN ele_slr_ord_wide_di ES ON E.shop_id = ES.shop_id AND  date_trunc = ES.pay_time
GROUP BY  date_trunc;

查询2:

WITH numbers as
(
    SELECT date_trunc('day', dd):: date
    FROM generate_series( '2019-10-01'::timestamp , '2019-10-31'::timestamp, '1 day'::interval) dd
)
SELECT date_trunc,COUNT(DISTINCT E.shop_id) ParticipatedStores, COUNT(DISTINCT ES.order_id) TotalOrders, SUM(ES.pay_amt) total_sales
FROM numbers n LEFT JOIN ele_slr_act_wide E ON   date_trunc BETWEEN E.start_time AND E.end_time
LEFT JOIN ele_slr_ord_wide_di ES ON E.shop_id = ES.shop_id AND  date_trunc = ES.pay_time
GROUP BY  date_trunc
ORDER BY CASE WHEN SUM(ES.pay_amt) IS NULL THEN 0 ELSE SUM(ES.pay_amt) END DESC
limit 1;

输出将

​

​