Cacher is the code snippet organizer for pro developers

SELECT sessioncount, timespent, cnt, expected
FROM (SELECT allcells.sessioncount, allcells.timespent, cells.cnt, (dim2.cnt * dim3.cnt)/dimall.cnt  as expected
      FROM (SELECT sc.sessioncount, ts.timespent
            FROM (SELECT distinct sessioncount FROM d3) sc JOIN
                 (SELECT distinct timespent FROM d3) ts
           ) allcells LEFT JOIN
           (SELECT sessioncount, timespent, sum(cnt) as cnt
            FROM d3
            GROUP BY sessioncount, timespent
           ) cells
           ON allcells.sessioncount = cells.sessioncount AND
              allcells.timespent = cells.timespent LEFT JOIN
           (SELECT sessioncount, SUM(cnt) AS cnt
            FROM d3
            GROUP BY sessioncount
           ) dim2
           ON allcells.sessioncount = dim2.sessioncount LEFT JOIN
           (SELECT timespent, SUM(cnt) AS cnt
            FROM d3
            GROUP BY timespent
           ) dim3
           ON allcells.timespent = dim3.timespent JOIN
           (SELECT SUM(cnt) AS cnt
            FROM d3
          ) dimall
     ) a

SELECT sessionnumber, sessioncount, timespent, (dim1.cnt * dim2.cnt * dim3.cnt)/(dimall.cnt*dimall.cnt) as expected
FROM (SELECT sessionnumber, SUM(cnt) as cnt
      FROM d3
      GROUP BY sessionnumber) dim1 JOIN
      (SELECT sessioncount, SUM(cnt) as cnt
       FROM d3
       GROUP BY sessioncount) dim2 JOIN
      (SELECT timespent, SUM(cnt) as cnt
       FROM d3
       GROUP BY timespent) dim3 JOIN
      (SELECT SUM(cnt) as cnt 
       FROM d3) 
 dimall;

SELECT sum(expected), sum(cnt) from (
SELECT sessioncount, timespent, cnt, expected
FROM (SELECT allcells.sessioncount, allcells.timespent,
             cells.cnt,
             (dim2.cnt * dim3.cnt)/cast(dimall.cnt as float) as expected
      FROM (SELECT sc.sessioncount, ts.timespent
            from (SELECT distinct sessioncount from d3) sc cross join
                 (SELECT distinct timespent from d3) ts
           ) allcells left join
           (SELECT sessioncount, timespent, sum(cnt) as cnt
            from d3
            group by sessioncount, timespent
           ) cells
           on allcells.sessioncount = cells.sessioncount and
              allcells.timespent = cells.timespent left JOIN
           (SELECT sessioncount, SUM(cnt) as cnt
            FROM d3
            GROUP BY sessioncount
           ) dim2
           ON allcells.sessioncount = dim2.sessioncount left JOIN
           (SELECT timespent, SUM(cnt) as cnt
            FROM d3
            GROUP BY timespent
           ) dim3
           ON allcells.timespent = dim3.timespent CROSS JOIN
           (SELECT SUM(cnt) as cnt
            FROM d3
           ) dimall
     ) a
) b