Subscribers

Suppose you're working on a content streaming app like Netflix which requires a subscription. Users can either have a monthly or an annual billing cycle. We have a table called transactions to store all payments for subscriptions.

Hopefully by now, you've gotten used to seeing an id column for each table. We have it here to. This is a unique identifier for each row (each transaction) in the table, which has no duplicates. Every table should have an ID, though it doesn't need to be called id.

There is also a subscriber_id. This represents the person who is making the transaction. Since a subscription involves repeated payments, each subscriber has many transactions. Hence, the same subscriber_id may show up many times, whereas the transaction ID, id, ie unique in this table.

Notice that we also have a new type, DATE. Intuitively this represents a date, for example July 4th 1776, though in databases it's usually written like 1776-07-04.

For this challenge, we want to select a list of subscriber_ids that had a valid subscription on 2022-01-01. Some of these transactions are for monthly billing cycles, and some are annual. A user has a valid subscription on 2022-01-01 if they have a monthly transaction within 1 month before 2022-01-01 or an annual transaction within 1 year before 2022-01-01.

Hints

• Monthly or annual look like 2 "cases" you need to handle.
• You can create an instance of a DATE by casting it from a string literal, CAST('2022-01-01' AS DATE), review CAST if you need.
• DATEs can be compared with operators just like numbers can, = < > <= >= !=.
• You can add or subtract intervals of time from DATEs using the INTERVAL type, e.g. CAST('2022-01-01' AS DATE) - CAST('1 month' AS INTERVAL).