bitternut

visitor_data_analytics

Oct 19th, 2021
735
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE VIEW `visitor_data_analytics`
  2.             AS
  3.             SELECT
  4.                 visitors.*,
  5.                 IFNULL(num_login, 0) as num_login,
  6.                 IFNULL(max_duration, 0) as max_duration,
  7.                 IFNULL(num_interaction, 0) as num_interaction,
  8.                 IFNULL(num_booth_visited, 0) as num_booth_visited,
  9.                 IFNULL(mobile_max_duration, 0) as mobile_max_duration,
  10.                 IFNULL(num_chat, 0) as num_chat
  11.             FROM visitors
  12.  
  13.             LEFT JOIN
  14.                 (SELECT visitor_id, COUNT(guest_books.id) AS num_interaction, COUNT(DISTINCT tenant_id) AS num_booth_visited
  15.                 FROM guest_books GROUP BY visitor_id) AS t_interaction
  16.             ON visitors.id = t_interaction.visitor_id
  17.  
  18.             LEFT JOIN
  19.                 (SELECT visitor_id, COUNT(event_trackers.id) AS num_login
  20.                 FROM event_trackers
  21.                 WHERE event_json LIKE '%visitorLogin%'
  22.                 GROUP BY visitor_id) AS t_login
  23.             ON visitors.id = t_login.visitor_id
  24.  
  25.             LEFT JOIN
  26.                 (SELECT visitor_id,
  27.                     MAX(
  28.                         CASE
  29.                             WHEN JSON_EXTRACT(event_json, '$.event_type') = 'playInterval' then (JSON_EXTRACT(event_json, '$.duration') / 60)
  30.                             WHEN JSON_EXTRACT(event_json, '$.event_type') = 'playIntervalTimestamp' then TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(JSON_EXTRACT(event_json, '$.startTime')), FROM_UNIXTIME(JSON_EXTRACT(event_json, '$.currentTime')))
  31.                         END
  32.                         ) AS mobile_max_duration
  33.                 FROM event_trackers
  34.                 WHERE
  35.                     event_json LIKE '%playInterval%' OR event_json LIKE '%playIntervalTimestamp%'
  36.                 GROUP BY visitor_id) AS t_mobile_duration
  37.             ON visitors.id = t_mobile_duration.visitor_id
  38.  
  39.             LEFT JOIN
  40.                 (SELECT visitor_id, COUNT(event_trackers.id) AS num_chat
  41.                 FROM event_trackers
  42.                 WHERE event_json LIKE '%chat%' OR event_json LIKE '%contact%'
  43.                 GROUP BY visitor_id) AS t_chat
  44.             ON visitors.id = t_chat.visitor_id
  45.  
  46.             LEFT JOIN
  47.                 (SELECT visitor_id, MAX(CAST(REPLACE(REPLACE(event_json, CONCAT(SUBSTRING_INDEX(event_json,'fps-',1),'fps-'), ''), CONCAT('s-avg',SUBSTRING_INDEX(event_json,'s-avg',-1)),'') AS INTEGER)) AS max_duration
  48.                 FROM event_trackers
  49.                 JOIN visitors ON visitors.id = event_trackers.visitor_id
  50.                 WHERE event_json LIKE '%fps%' AND event_json NOT LIKE '%fps-view%'
  51.                 GROUP BY visitor_id) AS t_duration
  52.             ON visitors.id = t_duration.visitor_id
  53.  
  54.             ORDER BY visitors.id;
RAW Paste Data