CODEWITHSUNDEEP
sqldatabasepostgresql
Joey Clover
Joey Clover

Reputation: 776

Converting multiple simple queries into a single one?

I'm new to SQL so things will have to be explained like I'm 5. I'm trying to figure out how many people performed the event "clicked learn more" and then proceeded to perform the event "set trial status". I'm using Postgres so there is an additional AND payload->>'status' = true that I've left out to simplify it. Essentially I need the following psuedo-queries turned into a functional single query.

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;

How can I turn that into essentially one single SQL query, the second using the booking_ids from the first

Below is what the data looks like in the table.

row layout

Upvotes: 1

Views: 42

Answers (3)

Nick
Nick

Reputation: 147196

Without seeing table structures and sample data it's hard be completely certain, but if you want to get all the counts you mentioned in a comment to another answer, I think this query is what you need. 

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

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

i think you need group by name

SELECT name ,count(*)
FROM venue_events
WHERE   payload->>'status' = true
group by name

Upvotes: 0

Pepper Paige
Pepper Paige

Reputation: 117

SELECT count(*)
FROM venue_events
WHERE name = 'set trial status' 
AND booking_id IN
(
    SELECT DISTINCT booking_id
    FROM venue_events
    WHERE name = 'clicked learn more'
)
AND payload->>'status' = true;

Upvotes: 1

Related Questions