Use and avoid

LIMIT=${LIMIT:-6}

uvx chartcoach@latest catalog sql "
with modifier_counts as (
  select
    family,
    category,
    modifier,
    count(distinct guideline_id) as guidelines
  from guideline_labels
  where modifier in ('use', 'avoid')
  group by family, category, modifier
), paired as (
  select
    use_rows.family,
    use_rows.category,
    use_rows.guidelines as use_guidelines,
    avoid_rows.guidelines as avoid_guidelines,
    use_rows.guidelines + avoid_rows.guidelines as total_guidelines,
    round(avoid_rows.guidelines::double / (use_rows.guidelines + avoid_rows.guidelines), 2) as avoid_share
  from modifier_counts use_rows
  join modifier_counts avoid_rows
    on avoid_rows.family = use_rows.family
   and avoid_rows.category = use_rows.category
   and avoid_rows.modifier = 'avoid'
  where use_rows.modifier = 'use'
)
select
  family,
  category,
  use_guidelines,
  avoid_guidelines,
  total_guidelines,
  avoid_share
from paired
order by total_guidelines desc, avoid_share desc, family, category
limit ${LIMIT}
" --format table

FAMILY=chart
CATEGORY=line

uvx chartcoach@latest catalog query \
  --label "${FAMILY}:${CATEGORY}:use" \
  --limit 3 \
  --format table

uvx chartcoach@latest catalog query \
  --label "${FAMILY}:${CATEGORY}:avoid" \
  --limit 3 \
  --format table

uvx chartcoach@latest catalog query \
  --label "${FAMILY}:${CATEGORY}:use" \
  --limit 3 \
  --format jsonl |
  jq -s 'map({id, title, labels})'