visitor_data_analytics

CREATE OR REPLACE VIEW `visitor_data_analytics`
            AS
            SELECT
                visitors.*,
                IFNULL(num_login, 0) as num_login,
                IFNULL(max_duration, 0) as max_duration,
                IFNULL(num_interaction, 0) as num_interaction,
                IFNULL(num_booth_visited, 0) as num_booth_visited,
                IFNULL(mobile_max_duration, 0) as mobile_max_duration,
                IFNULL(num_chat, 0) as num_chat
            FROM visitors

            LEFT JOIN
                (SELECT visitor_id, COUNT(guest_books.id) AS num_interaction, COUNT(DISTINCT tenant_id) AS num_booth_visited
                FROM guest_books GROUP BY visitor_id) AS t_interaction
            ON visitors.id = t_interaction.visitor_id

            LEFT JOIN
                (SELECT visitor_id, COUNT(event_trackers.id) AS num_login
                FROM event_trackers
                WHERE event_json LIKE '%visitorLogin%'
                GROUP BY visitor_id) AS t_login
            ON visitors.id = t_login.visitor_id

            LEFT JOIN
                (SELECT visitor_id,
                    MAX(
                        CASE
                            WHEN JSON_EXTRACT(event_json, '$.event_type') = 'playInterval' then (JSON_EXTRACT(event_json, '$.duration') / 60)
                            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')))
                        END
                        ) AS mobile_max_duration
                FROM event_trackers
                WHERE
                    event_json LIKE '%playInterval%' OR event_json LIKE '%playIntervalTimestamp%'
                GROUP BY visitor_id) AS t_mobile_duration
            ON visitors.id = t_mobile_duration.visitor_id

            LEFT JOIN
                (SELECT visitor_id, COUNT(event_trackers.id) AS num_chat
                FROM event_trackers
                WHERE event_json LIKE '%chat%' OR event_json LIKE '%contact%'
                GROUP BY visitor_id) AS t_chat
            ON visitors.id = t_chat.visitor_id

            LEFT JOIN
                (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
                FROM event_trackers
                JOIN visitors ON visitors.id = event_trackers.visitor_id
                WHERE event_json LIKE '%fps%' AND event_json NOT LIKE '%fps-view%'
                GROUP BY visitor_id) AS t_duration
            ON visitors.id = t_duration.visitor_id

            ORDER BY visitors.id;