How to achieve the same results when correlated subquery is not supported

Question

I have 2 tables:

sessions: This table contains all the sessions that users have had. It contains 3 columns. uuid, user_id, occurred_at

purchases: This table contains all the purchases a user has made. It contains 3 columns: purchase_id, user_id, purchased_at

The question: For each session, find the number purchases the user had already made at the time of the session

pseudo code:

for session in sessions:
   current_session_user_id = session["user_id"]
   current_session_timestamp = session["occurred_at"]
   num_purchases_made_at_the_time = query(
      SELECT count(*) FROM purchases 
      WHERE
          purchases.user_id =  current_session_user_id
          AND purchases.purchased_at < current_session_timestamp
   )

Note: Non-equality correlated subquery expression is not supported in my database (vertica)

Some ideas because I am not able to use correlated subquery:

1. Write a CTE that indexes when each order count occurred for each user and then join. ()

Answer 1

Did you mean CTE as Common Table Expression or... CTE as Conditional True Event? :-)

If you have:

SQL> select * from sessions order by uuid;
 uuid | user_id |     occurred_at     
------+---------+---------------------
    1 |     100 | 1959-03-07 04:31:15
    2 |     100 | 1969-03-07 05:31:15
    3 |     200 | 1979-03-07 06:31:15
    4 |     200 | 1989-03-07 07:31:15
    5 |     300 | 1999-03-07 08:31:15

and:

SQL> select * from purchases order by purchase_id;
 purchase_id | user_id |    purchased_at     
-------------+---------+---------------------
        1000 |     100 | 1959-03-07 04:32:15
        1001 |     100 | 1959-03-07 04:33:15
        1002 |     100 | 1969-03-07 05:32:15
        1003 |     200 | 1979-03-07 06:32:15
        1004 |     300 | 1999-03-07 08:32:15

Then:

SQL> select max(a.user_id) as user_id, a.occurred_at, max(a.cte) as num_purchases 
     from ( select s.user_id, s.occurred_at,
                   conditional_true_event(purchased_at < occurred_at)
                   over(partition by s.user_id order by s.occurred_at) as cte 
            from purchases p inner join sessions s 
                 on p.user_id = s.user_id 
     ) a 
     group by a.occurred_at 
     order by 1, 2;
 user_id |     occurred_at     | num_purchases 
---------+---------------------+---------------
     100 | 1959-03-07 04:31:15 |             0
     100 | 1969-03-07 05:31:15 |             2
     200 | 1979-03-07 06:31:15 |             0
     200 | 1989-03-07 07:31:15 |             1
     300 | 1999-03-07 08:31:15 |             0