SQL Explorer - Playground Query

Playground Query

near "as": syntax error

Title

Connection

Description

SQL

with RECURSIVE r as (
  SELECT 
  0 as len
  , cidr '0.0.0.0/0' as parent
  , child.child as child
  , case when child.child = (cidr '0.0.0.0/1') then 7000 else 3000 end :: float as views
  , case when child.child = (cidr '0.0.0.0/1') then 230 else 20 end :: float as sales
  , floor(random() * 10) / 10 as next_split
  , floor(random() * 10) / 10 as next_sales_split
  , row_number() over () as row
  from (
      select set_masklen('0.0.0.0/0',  1) as child
      union
      select set_masklen(broadcast(set_masklen('0.0.0.0/0',  1)) + 1,  1)::cidr
    ) child
  UNION
  SELECT len + 1 as len
  , r.child :: cidr as parent
  , cs.child :: cidr as child 
  , round(case 
      when row_number() over (partition by r.child order by cs) = 1 
        then r.views * r.next_split 
      else r.views * (1 - r.next_split) end) as views
  , round(case 
      when row_number() over (partition by r.child order by cs) = 1 
        then r.sales * r.next_sales_split 
      else r.sales * (1 - r.next_sales_split) end) as sales
  , floor(random() * (case when len >= 2 then 5 else 10 end)) / (case when len >= 2 then 5 else 10 end) as next_split
  , floor(random() * (case when len >= 2 then 5 else 10 end)) / (case when len >= 2 then 5 else 10 end) as next_sales_split
  , row_number() over (partition by r.child order by cs.child) as row
  from r
  join lateral (
      select set_masklen(r.child, masklen(r.child) + 1) as child
      union
      select set_masklen(broadcast(set_masklen(r.child, masklen(r.child) + 1)) + 1, masklen(r.child) +1)::cidr
    ) cs on true
                 WHERE len < 5
)
-- select * from r
, s as (
    SELECT len, parent, array_agg(child) as children
    , sum(views) as views
    , sum(sales) as sales
    --   '"' || parent || '" -- ' || '"' || child || '"'
    from r
    group by len, parent
    order by len, parent
)
select * from r

SQL Assistant

(?)