You& Data_Science & Life

SQL_programmers_quiz_2_sum_min_max

04 Oct 2023 SQL

2. SUM, MAX, Min

■ Q_1


WITH G  AS (
  SELECT *
  FROM (
    SELECT 
      user_pseudo_id
      , DATE(event_timestamp_kst) AS SESSION_DAY
      , HOUR(event_timestamp_kst) AS SESSION_HOUR
      , DAYNAME(event_timestamp_kst)  AS SESSION_WEEKDAY
    FROM ga
    WHERE event_name LIKE  '%click%') AS G0
  -- GROUP BY user_pseudo_id,  SESSION_HOUR, SESSION_WEEKDAY
  )

SELECT 
  SESSION_WEEKDAY
  , COUNT(DISTINCT SESSION_DAY) AS NUMBER_OF_WEEKDAY
  , ROUND(COUNT(DISTINCT user_pseudo_id)) AS SUM_USER_BY_WEEKDAY 
  , ROUND(COUNT(DISTINCT user_pseudo_id) / COUNT(DISTINCT SESSION_DAY)) AS AVG_USER_BY_WEEKDAY 
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 0 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_0
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 1 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_1
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 2 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_2
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 3 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_3
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 4 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_4
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 5 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_5
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 6 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_6
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 7 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_7
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 8 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_8
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 9 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_9
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 10 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_10
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 11 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_11
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 12 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_12
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 13 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_13
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 14 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_14
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 15 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_15
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 16 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_16
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 17 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_17
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 18 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_18
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 19 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_19
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 20 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_20
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 21 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_21
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 22 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_22
  , ROUND(COUNT(DISTINCT CASE WHEN SESSION_HOUR = 23 THEN user_pseudo_id END) /  COUNT(DISTINCT SESSION_DAY)) AS  HOUR_23
  FROM G
  GROUP BY SESSION_WEEKDAY

limit 10



Related posts