Practice · History · Hard

Longest-reigning pharaoh per era

For each era, return the era, the name of its longest-reigning pharaoh, and that reign length (as years_reigned). Use a window function to rank within each era. Order by years_reigned, longest first.

Solve this challenge →

Runs in your browser. No signup needed.

What you are given

Use pharaohs. ROW_NUMBER() OVER (PARTITION BY era ORDER BY length DESC) picks the top ruler per era.

Starting point

SELECT era, name, years_reigned FROM (
  SELECT era, name, (reign_end - reign_start) AS years_reigned,
         ROW_NUMBER() OVER (PARTITION BY era ORDER BY (reign_end - reign_start) DESC) AS rn
  FROM pharaohs
) WHERE rn = 1
ORDER BY years_reigned DESC;

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.

Did you know

PARTITION BY is how analysts get a “best per group” answer in one query — the workhorse behind “top product per region” dashboards. (SQLite window-function docs)

More History challenges

New Kingdom pharaohs Easy
The five longest reigns Easy
Pharaohs per era Medium
Monuments and their builder’s dynasty Hard
Rulers who reigned over 30 years Medium

Open it in the playground →