drop
  table
    if exists avg_sales_clen
;

create
  TEMP table
    avg_sales_clen as select
      [a.date:aggregation] as cas
      , avg(total) as avg_clen
    from
      (
        select
          t.id_sale
          , t.date
          , sum(t.total) over(partition by t.id_sale) as total
        from
          static_45050.transactions t
          left join static_45050.cards_accounts c on
            t.id_card = c.cardnumber
          left join static_45050.stores s on
            s.storecode = t.store
        where
          c.cardnumber <> 'no_card'
          and [s.storecode=Prodejny]
          and [t.date=daterange]
          and t.event <> 'registrace_do_klubu'
      )
      a
    group by
      1
;

drop
  table
    if exists avg_sales_neclen
;

create
  TEMP table
    avg_sales_neclen as select
      [a.date:aggregation] as cas
      , avg(total) as avg_neclen
    from
      (
        select
          t.id_sale
          , t.date
          , sum(t.total) over(partition by t.id_sale) as total
        from
          static_45050.transactions t
          left join static_45050.cards_accounts c on
            t.id_card = c.cardnumber
          left join static_45050.stores s on
            s.storecode = t.store
        where
          c.cardnumber = 'no_card'
          and [s.storecode=Prodejny]
          and [t.date=daterange]
          and t.event <> 'registrace_do_klubu'
      )
      a
    group by
      1
;

select
  clen.cas as date
  , clen.avg_clen as "avg clen"
  , neclen.avg_neclen as "avg neclen"
from
  avg_sales_neclen neclen
  left join avg_sales_clen clen on
    clen.cas = neclen.cas