Queries Quality and Percentage

The problem, with the data in front of you

The original lesson showed the answer but never the rows, so the worked arithmetic was impossible to check. Here is the actual Queries sample:

query_name	result	position	rating
Dog	Golden Retriever	1	5
Dog	German Shepherd	2	5
Dog	Mule	200	1
Cat	Shirazi	5	2
Cat	Siamese	3	3
Cat	Sphynx	7	4

quality = the average of rating / position over a query's rows. poor_query_percentage = the share of a query's rows with rating < 3, as a percent. Both rounded to 2 dp.

The portable solution (and why the obvious one breaks across engines)

The natural MySQL answer:

SELECT query_name,
       ROUND(AVG(rating / position), 2)                        AS quality,
       ROUND(AVG(rating < 3) * 100, 2)                         AS poor_query_percentage
FROM Queries
GROUP BY query_name;

Two things here are engine-specific landmines worth internalizing:

1. Integer division. In MySQL / always yields a decimal, so 5/2 = 2.5. In PostgreSQL and SQLite, integer÷integer is integer division: 5/2 = 2, 1/200 = 0. Dog's quality would silently become AVG(5,2,0)=2.33 instead of 2.50 — a wrong answer with no error. Make it portable by forcing decimal arithmetic: AVG(rating * 1.0 / position) or AVG(rating::numeric / position).
2. Boolean-as-number. rating < 3 evaluates to 1/0 in MySQL and SQLite, so AVG(rating < 3) * 100 is the percentage directly. PostgreSQL has no implicit bool→int, so the portable form is the explicit indicator:

-- Portable across MySQL / PostgreSQL / SQLite
SELECT query_name,
       ROUND(AVG(rating * 1.0 / position), 2)                                  AS quality,
       ROUND(AVG(CASE WHEN rating < 3 THEN 1.0 ELSE 0 END) * 100, 2)           AS poor_query_percentage
FROM Queries
GROUP BY query_name;

Notice the trick in the second line: averaging a 0/1 indicator is the proportion, so AVG(...) * 100 gives the percentage in one step — no separate SUM(...) / COUNT(*) needed, and no risk of integer division on the count.

Traced result

Dog: AVG(5, 2.5, 0.005) = 2.50; one of three rows (rating 1) is poor → 33.33%. Cat: AVG(0.4, 1.0, 0.571) = 0.66; one of three rows (rating 2) is poor → 33.33%.

query_name	quality	poor_query_percentage
Dog	2.50	33.33
Cat	0.66	33.33

Takeaways

• Averaging a 0/1 indicator gives a proportion — the cleanest way to express "% of rows matching X".
• int / int truncates in PostgreSQL/SQLite but not MySQL — multiply by 1.0 or cast when you need a real ratio.
• Always pin the sample rows when you present worked arithmetic, so the reader can verify rather than trust.

---

Re-authored for this guide: the prior version hid the sample rows and used sloppy boolean notation. Pattern: LeetCode 1211. Cross-engine division behaviour per the MySQL, PostgreSQL and SQLite arithmetic docs.

I just read a lesson on **Queries Quality and Percentage** (Databases) and want to truly understand it. Explain Queries Quality and Percentage from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Queries Quality and Percentage** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Queries Quality and Percentage** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Queries Quality and Percentage** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.