0

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?

Lasha Dolenjashvili
  • 166
  • 8
  • A good way to solve this problem is to create a new table, maybe called `sms_sent_log`. In each row put the account number and the date for each SMS you send. Then use it to exclude accounts from consideration. – O. Jones Nov 21 '22 at 11:34
  • Thanks for the answer. However, as I've mentioned, we can't automate the table creation process. I mean, we can't create `sms_sent_log` and then use it in the same query. We only have VIEWs. – Lasha Dolenjashvili Nov 21 '22 at 11:50

1 Answers1

0

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/

dacopan
  • 51
  • 2
  • 5