SQL Query for Author Counts | Dataford Interview Questions

Impact wants to identify authors with a substantial publishing history in its book catalog. Write a PostgreSQL query to find every author who has published at least five books.

Requirements

Join authors to books using the author relationship.
Count only books whose publication_status is published.
Return the author ID, author name, and published book count for authors with at least five published books.
Sort by published book count descending, then author name ascending.

Schema

authors

Column	Type	Description
author_idPK	INT	Unique identifier for the author
author_name	VARCHAR(150)	Author's display name
country	VARCHAR(80)	Author's country of residence

books

Column	Type	Description
book_idPK	INT	Unique identifier for the book
author_id	INT	Author reference, nullable for incomplete catalog records
title	VARCHAR(200)	Book title
publication_status	VARCHAR(20)	Current publication state

Your question is SQL Query for Author Counts. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem