玩家每日最大关卡进度分布

需求如题，但是要注意：每一天的最大关卡包括当天之前的所有最大通关进度，比如一个用户在1号通过的10关，2号没有打，也会算在2号第十关的人数里

第一次写的时候就没有考虑到这个问题，直接把每个用户每天的最大关卡查出来然后自连接去算前x天的最大关卡，但是这样如果玩家第二天没有关卡数据，第二天的关卡分布就没有这个用户，也就是玩家在这天至少要有活动关卡的行为才会算进来

修正方法：用一个date连接每天的关卡数据，然后用max over 窗口函数计算每天的最大关

 注:：要考虑如何把用户缺失的“第二天”补上去，然后再max over就行，就是相当于再建一个完整日期的临时表，去CROSS JOIN 每日的关卡表，然后就把缺失的天补上啦

SELECT "$client",
       CASE
           WHEN pay."$uid" IS NOT NULL THEN 'pay_user'
           ELSE 'free_user'
       END AS pay_type,
       "$datesub",
       max_act_level,
       count(DISTINCT b."$uid") num
FROM
  (SELECT DISTINCT "$uid",
                   "$client"
   FROM app1242.track_user_view
   WHERE "$datesub"= format_datetime(now()- interval '1' DAY,'yyyy-MM-dd')

   ) t
JOIN
  (SELECT "$uid"
   FROM app1242.track_log_view
   WHERE "$action" IN ('$login',
                       'levelEnd')
     AND "$datesub" >= '2024-02-03'
     AND "$datesub" <= '2024-02-18'
   GROUP BY"$uid" ) a ON t."$uid"= a."$uid"
JOIN
  ( SELECT "$uid",
           day."$datesub",
           max(max_act_level) over (partition BY "$uid" ) max_act_level
   FROM
     (SELECT "$datesub"
      FROM app1242.track_log_view
      WHERE "$action" IN ('levelStart',
                          'levelEnd')
        AND "$datesub" >= '2024-02-03'
        AND "$datesub" <= '2024-02-18'
       
      GROUP BY "$datesub") DAY
   CROSS JOIN
     (SELECT "$uid",
             "$datesub",
             max(LEVEL) max_act_level
      FROM app1242.track_log_view
      WHERE "$action" IN ('levelStart',
                          'levelEnd')
        AND "$datesub" >= '2024-02-03'
        AND "$datesub" <= '2024-02-18'
       
      GROUP BY "$uid",
               "$datesub") act_level
   WHERE DAY."$datesub" >= act_level."$datesub" ) b ON a."$uid" = b."$uid"
LEFT JOIN
  (SELECT DISTINCT "$uid"
   FROM app1242.track_log_view
   WHERE "$action" IN('$pay')
     AND "$datesub" <= format_datetime(now()- interval '1' DAY,'yyyy-MM-dd')) pay ON a."$uid" = pay."$uid"
GROUP BY 1,
         2,
         3,
         4
ORDER BY 1,
         2,
         3,
         4