SQLite JSON 태그 빈도 정렬

-- 태그별 빈도 계산
SELECT json_each.value AS tag, COUNT(*) AS frequency
FROM example, json_each(example.tags)
GROUP BY json_each.value
ORDER BY frequency DESC, tag ASC;

-- 빈도순 정렬된 태그를 JSON 배열로
SELECT json_group_array(tag) AS tags_by_frequency
FROM (
  SELECT json_each.value AS tag
  FROM example, json_each(example.tags)
  GROUP BY json_each.value
  ORDER BY COUNT(*) DESC, tag ASC
);