SQL Challenges · WEEKLY QUACK CHALLENGE!
THE BADGE TALLY
Difficulty: medium
Camp Chief Ruddy tips a satchel of quest logs onto the table, badges spilling everywhere. "The jamboree trophy goes to the patrol with the most merit points, Duckbert, and the new camp app saved every log as one big tangle of brackets. The Cattail Crew keeps waving a summit flight the judges threw out. The Reed Runners filed a whole stack of logs, and two of them are blank. Some badges don't even say how many times they were earned! Tally it properly and tell me who lifts the trophy before the…
Your task
Return the name of the patrol with the most merit points, as plain text without JSON quotes. Each row of `quest_logs` holds one quest log as a JSON document with the `patrol`, a `status`, and a `badges` array; each badge has `points` and usually `times`. Only `approved` logs count; `rejected` ones do not. A badge is worth its `points` multiplied by its `times`, and a badge with no `times` was earned once. A patrol's merit points are the sum over every badge in its approved logs. On a tie, return the alphabetically first patrol name. You will be working with the `quest_logs` table.
Tables
quest_logs (20 rows)
| column | type |
|---|---|
log_id | INTEGER |
log | JSON |
Database: https://dbquacks.com/data/databases/weekly_202646_19_badge_tally.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/19.md
More: practice SQL exercises online · SQL interview questions