gistfile1.txt

WITH content_tags AS (
  SELECT 
    json_extract(content,'$.tags') AS elements,
    lower(hex(event_hash)) AS hash,
    e.created_at AS reported_at
  FROM event AS e 
  WHERE kind = 1984 AND e.created_at >= $__from / 1000 and e.created_at < $__to / 1000
)
SELECT 

  CASE 
    WHEN json_array_length(ct.elements) == 1 THEN json_extract(json_extract(ct.elements, '$[0]'),'$[2]')
    WHEN json_array_length(ct.elements) == 2 THEN json_extract(json_extract(ct.elements, '$[0]'),'$[2]')
    ELSE "NULL"
  END AS qualification,


  CASE 
    WHEN json_array_length(ct.elements) == 1 THEN  "user"
    WHEN json_array_length(ct.elements) == 2 THEN  "note"
    WHEN json_array_length(ct.elements) == 3 THEN  "relay"
    ELSE "NULL"
  END AS "report type",
  
  CASE 
    WHEN json_array_length(ct.elements) == 1 THEN json_extract(json_extract(ct.elements, '$[0]'),'$[1]')
    WHEN json_array_length(ct.elements) == 2 THEN json_extract(json_extract(ct.elements, '$[1]'),'$[1]')
    WHEN json_array_length(ct.elements) == 3 THEN  NULL
    ELSE "NULL"
  END AS "reported content",

  lower(hex(ct.hash)) as "report event hash",

  ct.reported_at
FROM 
  content_tags AS ct
ORDER BY ct.reported_at DESC