Recursive query in RedShift

Question

I am very new to SQL and RedShift as well. I have two tables.

account_usage:

account_id | usage_month |  usage_cost | usage_plan      | usage_type
   1      | 06-01-2018  |   100$      | 2018 - Custom   | dining
   1      | 06-01-2018  |   40$       | 2018 - Standard | office_supply
   2      | 06-01-2018  |   20$       | 2018 - Standard | dining
   2      | 06-01-2018  |   30$       | 2018 - Custom   | office_supply
   3      | 06-01-2018  |   25$       | 2018 - Custom   | dining
   3      | 06-01-2018  |   22$       | 2018 - Standard | office_supply

account_structure:
account_id | account_parent_id | account_name
  1          |  3                | account_1
  2          |  3                | account_2
  3          |  0                | account_3

from these two tables, I want to build an aggregation table. In this table for each Id the total usage will be the sum of the usage of the same account + the usage of all its children's account. total_usage_by_type will be a json string that will accumulate the usage by usage_type in a json String.

account_usage_aggregations:
account_id | usage_month |  usage_plan  | total_usage | total_usage_by_type
1          | 06-01-2018  |  2018-Custom   | 100      |{"dining":100}
1          | 06-01-2018  |  2018-Standard | 40       |{"office_supply":40}
2          | 06-01-2018  |  2018-Custom   | 30       |{"office_supply":30}
2          | 06-01-2018  |  2018-Standard | 20       |{"dining":20}
3          | 06-01-2018  |  2018-Standard | 82       |{"office_supply":62 , "dining": 20}
3          | 06-01-2018  |  2018-Custom   | 155      |{"office_supply":100, "dining": 55}

I wanted to solve this in a recursive query and started with this

  with C as (
     select account_id,
        usage_type,
        usage_cost,
        account_id as RootID
    from account_usage
    union all
    select account_id,
        usage_type,
        usage_cost,
        C.RootID
    from account_usage
    join C 
    on account_structure.account_parent_id = C.account_id
 ) select * from C;

But I ran into the following error.

 [Amazon](500310) Invalid operation: relation "c" does not exist;

1 statement failed.

Is there a way to execute recursive queries in redShift ?

Answer 1

Recursive CTEs are now supported in Redshift starting April 29th, 2021 using the WITH RECURSIVE syntax:

• https://aws.amazon.com/about-aws/whats-new/2021/04/amazon-redshift-announces-support-for-heirarchical-data-queries-with-recursive-cte/
• https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html#r_WITH_clause-recursive-cte

WITH RECURSIVE c AS (
    SELECT account_id, usage_type, usage_cost, account_id AS RootID
    FROM account_usage
    UNION ALL
    SELECT account_id, usage_type, usage_cost, c.RootID
    FROM account_usage
    JOIN c ON account_structure.account_parent_id = c.account_id
          )
SELECT *
FROM C;