SQL Join and Aggregation | Dataford Interview Questions

Cotality's Knowledge Library stores authors and their published books in separate tables. Write a PostgreSQL query that reports how many books each author has written.

Requirements

Return every author, including authors who have no matching books.
Count books using the book primary key, so authors with no books appear with a count of zero.
Return the author ID, author name, and book count, ordered by book count descending and author name ascending for ties.

Schema

authors

Column	Type	Description
author_idPK	INTEGER	Unique author identifier
author_name	VARCHAR(100)	Author's display name

books

Column	Type	Description
book_idPK	INTEGER	Unique book identifier
author_id	INTEGER	Reference to authors.author_id; NULL indicates no assigned author
book_title	VARCHAR(200)	Title of the book

Your question is SQL Join and Aggregation. 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