How to determine membership on transaction dates using SQL?

Question

I have two very large tables in a DB.

One is regarding membership and when users were members:

User|Date|Membership
1111|2020-12-01 06:00:00|False
1111|2020-12-20 18:00:00|True
1111|2020-12-30 12:00:00|False
2222|2020-12-01 06:00:00|True
2222|2020-12-20 18:00:00|False
2222|2020-12-30 12:00:00|True
...

And the other is regarding those users' transactions:

User|Date|Transaction
1111|2020-12-02 06:00:00|3.00
1111|2020-12-19 18:00:00|2.00
1111|2020-12-29 12:00:00|4.00
2222|2020-12-02 06:00:00|1.00
2222|2020-12-19 18:00:00|2.00
2222|2021-01-06 12:00:00|4.00
...

I'd like to determine if the user was a member or not when they made the transaction with a new field in the latter table that, in this case, would be the following:

User|Date|Transaction|Was_Member
1111|2020-12-02 06:00:00|3.00|False
1111|2020-12-19 18:00:00|2.00|False
1111|2020-12-29 12:00:00|4.00|True
2222|2020-12-02 06:00:00|1.00|True
2222|2020-12-19 18:00:00|2.00|True
2222|2021-01-06 12:00:00|4.00|True
...

How can I efficiently do something like this in SQL where I stitch time ranges together in order to detect membership? Any SQL language is fine, just want to understand the method.

question from:https://stackoverflow.com/questions/65517016/how-to-determine-membership-on-transaction-dates-using-sql

Answer 1

You can join two tables based on dates (less than) and find the latest record using analytical function as follows:

select user, date, transaction, membership as was_member from
(select t.user, t.date, t.transaction, m.membership,
       row_number() over (partition by t.user, t.date order by m.date desc) as rn
  from membership m join transactions t
    on t.user = m.user and t.date >= m.date) t
where rn = 1