tem_eaa_asset_uri

CREATE VIEW tem_eaa_asset_uri AS 
WITH parts AS (
  SELECT
    uri,
    -- remove query/fragments, trim slashes, then split into a JSON array of segments
    '["' || REPLACE(
      TRIM(
        substr(uri, 1,
          CASE
            WHEN instr(uri, '?') > 0 THEN instr(uri, '?') - 1
            WHEN instr(uri, '#') > 0 THEN instr(uri, '#') - 1
            ELSE length(uri)
          END
        ), '/'
      ),
    '/', '","') || '"]' AS j
  FROM uniform_resource
  WHERE uri LIKE '%eaa%'
),
assets AS (
  SELECT
    uri,
    j,
    json_array_length(j) AS len,
    json_extract(j, '$[' || (json_array_length(j) - 1) || ']') AS last_seg,
    json_extract(j, '$[' || (json_array_length(j) - 2) || ']') AS penult_seg
  FROM parts
),
clean_assets AS (
  SELECT
    uri,
    CASE
      WHEN len >= 2 AND last_seg GLOB '*.*' THEN penult_seg
      ELSE last_seg
    END AS asset
  FROM assets
)
SELECT
  asset AS asset,
  MIN(uri) AS uri
FROM clean_assets
WHERE asset IS NOT NULL
  -- ignore segments that look like a full timestamp: YYYY-MM-DD-HH-MM-SS
  AND asset NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]-[0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
GROUP BY asset
ORDER BY asset