sql - 将多个简单查询转换为单个查询？

我对sql还不熟悉，所以必须像5岁那样解释。我试图找出有多少人执行了“单击了解更多”事件，然后继续执行“设置试用状态”事件。我正在使用postgres，所以我省略了一个额外的AND payload->>'status' = true来简化它。本质上，我需要将下面的psuedo查询转换为一个功能单一的查询。

SELECT DISTINCT booking_id
FROM venue_events
WHERE name = 'clicked learn more'`;

SELECT count(*)
FROM venue_events
WHERE booking = booking_id AND name = 'set trial status' AND payload->>'status' = true;

我如何才能将其转换为一个SQL查询，第二个查询使用第一个查询中的预订ID
下面是表中的数据。

最佳答案

如果不查看表结构和示例数据，很难完全确定，但是如果您想将注释中提到的所有计数都获取到另一个答案，我认为这个查询就是您所需要的。

SELECT v1.booking_id AS booking_id,
       COUNT(v1.booking_id) AS Clicked_learn_more,
       COUNT(v2.booking_id) AS Set_trial_status_true,
       COUNT(v3.booking_id) AS Completed_booking
FROM (SELECT DISTINCT booking_id
      FROM venue_events
      WHERE name='clicked learn more' AND payload->>'status' = true) v1
LEFT JOIN (SELECT DISTINCT booking_id
           FROM venue_events
           WHERE name='set trial status' AND payload->>'status' = true) v2
    ON v2.booking_id = v1.booking_id
LEFT JOIN (SELECT DISTINCT booking_id
           FROM venue_events
           WHERE name='completed booking' AND payload->>'status' = true) v3
    ON v3.booking_id = v1.booking_id
GROUP BY v1.booking_id