Practice · History · Hard
For each pharaoh who built at least one monument, return the pharaoh name, their era, and how many monuments they built as monuments, most first (ties by name).
Runs in your browser. No signup needed.
pharaohs JOIN monuments on pharaohs.name = monuments.builder, then GROUP BY the pharaoh. Combine a join with aggregation.
SELECT p.name, p.era, COUNT(m.name) AS monuments
FROM pharaohs p
JOIN monuments m ON m.builder = p.name
GROUP BY p.name, p.era
ORDER BY monuments DESC, p.name;The reference solution is checked automatically when you run your query on the practice page — results are compared row by row, and order matters for this one.
Ramesses II was ancient Egypt’s greatest builder — the Ramesseum and Abu Simbel are only the most famous of his monuments. (Britannica)