1

I am trying to see if theres anyway i can implement this piece of code using only sql REDSHIFT

a = '''
    SELECT to_char(DATE '2022-01-01'
+ (interval '1 day' * generate_series(0,365)), 'YYYY_MM_DD') AS ym
'''
dfa = pd.read_sql(a, conn)


b = f'''
    select account_no, {','.join('"' + str(x) + '"' for x in dfa.ym)}
    from loan__balance_table
    where account_no = 
'''
dfb = pd.read_sql(b, conn)

the first query will yield something like this

| ym         |
| ---------- | 
| 2022_01_01 |
| 2022_01_02 |
...
| 2022_12_31|

Then i used string concatenation to combime the dates together and use then in the second query to select all columns in ym. The result of the second query should be something like this.

| account_no | 2022_01_01 | 2022_01_01 | ...
| ---------- | ---------- | ---------- | ...
| 1234       | 234,987.09 | 233,989.19 | ...

I just want to know if theres a way i can combine both queries together as one in sql without using python to concat the column_names.

I tried using CTE but i cant seem to get it right i dont even know if this is the right approach, The database is REDSHIFT

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Sirlivingstone
  • 19
  • 3
  • Can you describe the data in load_balance_table? Does it have data for the account balance for every date? Does it have date as a column? You are correct the first query is a recursive CTE which has been described in SO many times. Like - https://stackoverflow.com/questions/69883072/trying-to-create-a-date-table-in-redshift/69901710#69901710 but it isn't clear if this is needed. Does SELECT date, account_no FROM loan_balance_table WHERE account_no = 1234; produce what you want but pivoted? – Bill Weiner Feb 09 '23 at 15:51

0 Answers0