This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET @like_month := "2023-05%"; | |
SET @sport_id := 1; | |
SELECT COUNT(*) INTO @filmed FROM five_session fs JOIN field f ON f.id = fs.field_id JOIN sport s ON s.id = f.sport_id WHERE fs.started_at LIKE @like_month AND s.id = @sport_id AND fs.status NOT IN ('deleted', 'not_played') AND f.camera_urls IS NOT NULL GROUP BY s.id; | |
SELECT COUNT(*) INTO @total FROM five_session fs JOIN field f ON f.id = fs.field_id JOIN sport s ON s.id = f.sport_id WHERE fs.started_at LIKE @like_month AND s.id = @sport_id AND fs.status NOT IN ('deleted') AND f.camera_urls IS NOT NULL GROUP BY s.id; | |
SELECT COUNT(DISTINCT fs.id) INTO @seen FROM five_session_postmatch_log fspl JOIN five_session fs ON fs.id = fspl.five_session_id JOIN field f ON f.id = fs.field_id JOIN sport s ON s.id = f.sport_id WHERE fspl.created_at LIKE @like_month AND s.id = @sport_id GROUP BY s.id; | |
SELECT ROUND(SUM(TIME_TO_SEC(TIMEDIFF(fspl.updated_at, fspl.created_at))) / 3600, 2) INTO @time_spent FROM five_session_postmatch_log fspl JOIN five_session fs ON fs.id = fspl |