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)

columntype
log_idINTEGER
logJSON

Database: https://dbquacks.com/data/databases/weekly_202646_19_badge_tally.duckdb

This page as markdown: https://dbquacks.com/challenges/weekly/19.md

Play this challenge →

More: practice SQL exercises online · SQL interview questions