Five stats

five-monthly-stats.sql

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.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 COUNT(*) INTO @nb_events FROM five_session_event fse JOIN five_session fs ON fs.id = fse.five_session_id 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 GROUP BY s.id;
SELECT COUNT(*) INTO @with_score 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 padel_score REGEXP '[1-9]' AND fs.status NOT IN ('deleted', 'not_played') AND f.camera_urls IS NOT NULL GROUP BY s.id;
SELECT COUNT(DISTINCT fs.id) INTO @with_puntaco FROM five_session fs JOIN five_session_event fse ON fse.five_session_id = fs.id 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 fse.type IN ('puntaco', 'buzz') AND f.camera_urls IS NOT NULL GROUP BY s.name;
SELECT reference FROM sport_center;
SELECT @filmed;
SELECT @total;
SELECT ROUND(@filmed / @total, 2);
SELECT @seen;
SELECT ROUND(@seen / @filmed, 2);
SELECT @time_spent;
SELECT ROUND(@nb_events / @filmed, 2);
SELECT ROUND(@with_score / @filmed, 2);
SELECT ROUND(@with_puntaco / @filmed, 2);