Suppose I have a table with 2 columns: PURCHASE_DATE and ACCOUNT_NUMBER:
| PURCHASE_DATE | ACOUNT_NUMBER |
|---|---|
| 2022-01-01 | 123 |
| 2022-01-01 | 233 |
| 2022-01-01 | 333 |
| 2022-01-02 | 433 |
| 2022-01-02 | 233 |
| 2022-01-02 | 333 |
| 2022-01-03 | 123 |
| 2022-01-03 | 233 |
| 2022-01-03 | 335 |
I want to choose 1 account per day to send him a promotional SMS. (for example, first account when ordered by ACCOUNT_NUMBER). But on the second day, if the first account is the one to who I already sent an SMS the previous day, I need to exclude him/her and choose the next one. On the third day, I should exclude 2 accounts selected during the last 2 days, and so on.
Desired Output:
| PURCHASE_DATETIME | ACOUNT_NUMBER |
|---|---|
| 2022-01-01 | 123 |
| 2022-01-02 | 233 |
| 2022-01-03 | 335 |
As you can see, on 2022-01-03 accounts 123 and 233 were excluded.
I have to tackle this problem in Dremio where, as I know, we can only create views and we can't create temporary tables or Recursive CTEs.
I need to automate this process if possible. How would you approach this kind of problem?
In dremio you can create tables using CTE the use it in others query, you don't need automate the creation of tables, you can do both with query SQL Reference: https://docs.dremio.com/software/sql-reference/sql-commands/with/