sql - 根据 postgresql 中队列的初始日期进行计数

Question

我正在尝试通过免费试用开始日期获取免费试用和订阅的数量作为群组的一部分，我有如下表中的数据:

表测试数据

date         subscription   type         duration    subscriber_id   price
01/01/2019   monthly        free_trial   7 days      1               10.00
08/01/2019   monthly        paid         30 days     1               10.00
01/01/2019   monthly        free_trial   7 days      2               10.00
02/01/2019   monthly        free_trial   7 days      3               10.00
09/01/2019   monthly        paid         30 days     3               10.00
08/02/2019   monthly        paid         30 days     3               10.00

所以 subscriber_id 1 从 2019 年 1 月 1 日开始有一次免费试用和一次订阅，subscriber_id 2 从同一日期只有一次免费试用，而 subscriber_id 3 有一次免费试用、一次订阅和一次续订，这不应该被包括在内。我想得到这样的东西:

date          free_trials      subscription
01/01/2019    2                1
02/01/2019    1                1

但我对如何获取订阅计数部分感到困惑。

到目前为止，我得到了以下信息:

SELECT 
   date, 
   COUNT(CASE WHEN type = 'free_trial' THEN 1 ELSE NULL END ) AS free_trials 
FROM test_data
GROUP BY date
ORDER BY date

感谢任何帮助、评论或教程链接。谢谢

Answer 1

我可以做一个 self 加入，让你完成大部分工作

select free_trial.date
, count(distinct free_trial.subscriber_id) as free_trial
, count(distinct paid.type) as subscription 
from test_data free_trial
left join test_data paid on free_trial.subscriber_id = paid.subscriber_id
                        and paid.type = 'paid'
where free_trial.type = 'free_trial'
group by free_trial.date

我想不通的是“续订”有什么不同会导致它被排除在外。