Back

Answered by Waytan

WITH RECURSIVE cte (id, name, revenue_billions) AS (
	(SELECT 
  		tmp.id,
  		tmp.name,
  		revenue_billions
  	FROM wayne_enterprises AS a
  	INNER JOIN wayne_enterprises_org AS b
  	ON b.parent_id = a.id
    JOIN wayne_enterprises AS tmp
    ON tmp.id = b.id
    WHERE a.name = "Wayne Research Institute")
    UNION ALL
    (SELECT 
  		tmp1.id,
  		tmp1.name,
  		b1.revenue_billions
  	FROM wayne_enterprises AS a1
  	INNER JOIN wayne_enterprises_org AS b1
  	ON b1.parent_id = a1.id
    JOIN wayne_enterprises AS tmp1
    ON tmp1.id = b1.id
    JOIN cte
    ON cte.name = a1.name))
SELECT SUM(revenue_billions) as total_revenue FROM cte;

Answered by gurneet kaur

with recursive cte (id,name, revenue_billions) as (
  -- initialization
   select 
      b.id, 
      tmp.name,
      revenue_billions
      from wayne_enterprises a
      join wayne_enterprises_org b
      on b.parent_id = a.id 
      join wayne_enterprises tmp on tmp.id = b.id
      where a.name ='Wayne Research Institute'
  UNION ALL 
  -- recursive execution
  select 
      b1.id, 
      tmp1.name,
      b1.revenue_billions 
      from wayne_enterprises a1
      join wayne_enterprises_org b1
      on b1.parent_id = a1.id 
      join wayne_enterprises tmp1 on tmp1.id = b1.id
  	  join cte
  		on a1.name = cte.name
) select SUM(revenue_billions) as total_revenue from cte;

Answered by Pfwd44

with recursive cte (id,name, revenue_billions) as (
  -- initialization
   select 
      b.id, 
      tmp.name,
      revenue_billions
      from wayne_enterprises a
      join wayne_enterprises_org b
      on b.parent_id = a.id 
      join wayne_enterprises tmp on tmp.id = b.id
      where a.name ='Wayne Research Institute'
  UNION ALL 
  -- recursive execution
  select 
      b1.id, 
      tmp1.name,
      b1.revenue_billions 
      from wayne_enterprises a1
      join wayne_enterprises_org b1
      on b1.parent_id = a1.id 
      join wayne_enterprises tmp1 on tmp1.id = b1.id
  	  join cte
  		on a1.name = cte.name
) select SUM(revenue_billions) as total_revenue from cte;

Answered by anonymousUser

with recursive cte (id,name, revenue_billions) as (
  -- initialization
   select 
      b.id, 
      tmp.name,
      revenue_billions
      from wayne_enterprises a
      join wayne_enterprises_org b
      on b.parent_id = a.id 
      join wayne_enterprises tmp on tmp.id = b.id
      where a.name ='Wayne Research Institute'
  UNION ALL 
  -- recursive execution
  select 
      b1.id, 
      tmp1.name,
      b1.revenue_billions 
      from wayne_enterprises a1
      join wayne_enterprises_org b1
      on b1.parent_id = a1.id 
      join wayne_enterprises tmp1 on tmp1.id = b1.id
  	  join cte
  		on a1.name = cte.name
) select SUM(revenue_billions) as total_revenue from cte;