SQL
AخA
WITH video_length AS (
SELECT
img_minor_mime,
IF(img_minor_mime = 'ogg',
ROUND(COALESCE(JSON_EXTRACT(img_metadata, '$.data.length'), SUBSTR(REGEXP_SUBSTR(img_metadata, '(s:6:"length";d:)[0-9]*\.?[0-9]*'), 16), 0), 3),
ROUND(COALESCE(JSON_EXTRACT(img_metadata, '$.data.playtime_seconds'), SUBSTR(REGEXP_SUBSTR(img_metadata, '(s:16:"playtime_seconds";d:)[0-9]*\.?[0-9]*'), 27), 0), 3)
) AS video_length_seconds
FROM image
WHERE
img_media_type = 'VIDEO'
)
SELECT
img_minor_mime,
COUNT(1) AS num_files,
SUM(video_length_seconds) AS total_video_length_seconds
FROM video_length
WHERE
video_length_seconds < (60 * 60 * 24)
GROUP BY
img_minor_mime
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.