Untitled query #63328

SQL

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
SQL

Checking query status...
