Get total Car sessions SQL-CodePudding

I have a table Transactions with id, type, guest_id, timestamp.

I want to get the total Car sessions for the last two months. (assuming all transactions with type 'Car' within the same hour from the same guest constitute 1 session). I tried

SELECT  
    (
        SELECT COUNT(*)::int
        FROM transactions t
        WHERE t.type = 'Car'
        AND t.timestamp  <= gs   '1 hour'::interval
          AND t.timestamp > gs
        group by t.guest_id
    ) AS count
    FROM generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs        
    ORDER BY gs.date ASC

but I get an error ERROR: more than one row returned by a subquery used as an expression

How do I go about this ?

CodePudding user response：

Looks like you want a join of your table and the generated series

    SELECT t.guest_id, gs.dt, COUNT(*) cnt
    FROM transactions t
    JOIN generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs(dt) 
      ON t.timestamp  <= gs.dt   '1 hour'::interval
        AND t.timestamp > gs.dt
    WHERE t.type = 'Car'
    GROUP BY t.guest_id, gs.dt
    ORDER BY t.guest_id, gs.dt

CodePudding user response：

try in this way

SELECT  
    (
        SELECT COUNT(*)
        FROM transactions t
        WHERE t.type = 'Car'
        AND t.timestamp  <= gs   '1 hour'::interval
          AND t.timestamp > gs
    ) AS count
    FROM generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs        
    ORDER BY gs.date ASC