JOIN on two sub queries

SQL++
2 
SELECT t3.date, t3.totalAC, t3.totalDmn, t3.iC,
       (SELECT t5.tCT, ARRAY_AGG({t5.SW, t5.wCnt}) AS CWAC
        FROM t3.times AS t5
        GROUP BY t5.tCT)  AS WW
FROM ( SELECT t2.date, t2.totalAC, t2.totalDmn,
              (SELECT d.hs, SUM(d.cnts) AS cnts FROM t2.hs AS d GROUP BY d.hs) AS iC,
              (SELECT d.tCT, d.SW, SUM(d.wCnts) AS wCnt FROM t2.times AS d GROUP BY d.tCT, d.SW) AS times
       FROM ( SELECT t1.date AS date,
              SUM(t1.cnts) AS totalAC,
              SUM(t1.dmn) AS totalDmn,
              ARRAY_AGG({t1.hs, t1.cnts}) AS hs,
              ARRAY_AGG({t1.wCnts, t1.tCT, "SW":t1.sW || " " || IFNULL(t1.eW,"")}) AS times
              FROM (SELECT t.sAN AS acct,
                            t.mD as date,
                            t.hSC AS hs,
                            t.dim as dmn,
                            t.cd as tCT,
                            t.sT as sW,
                            t.eT as eW,
                            COUNT(1) AS cnts,
                            COUNT(t.sW) as wCnts
                    FROM  BUCKET_Viewable  AS t
                    WHERE t.sAN IN ["test"] AND t.mD BETWEEN "2019-03-01" AND "2019-03-25"
                    GROUP BY t.mD, t.dim, t.sAN, t.hSC, t.cd, t.sT, t.eT
              ) AS t1
       GROUP BY t1.date) AS t2
     ) AS t3;
1 Like