SQL Grouping With Conditions | Dataford Interview Questions

Your question is SQL Grouping With Conditions. Start with the requirements and the two tables on the right.

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

Problem

CarGurus wants to compare listing-quality marks across dealer groups. Write a PostgreSQL query that calculates the average mark for each dealer group using only listings with marks greater than 500.

Requirements

Join dealer_groups to listings using the dealer group identifier.
Exclude listings with marks less than or equal to 500, including NULL marks.
Return groups with at least two qualifying listings, their qualifying listing count, and their average mark rounded to two decimal places.
Sort results by average mark from highest to lowest, then group name alphabetically.

Schema

dealer_groups

Column	Type	Description
group_idPK	INT	Unique dealer group identifier
group_name	VARCHAR(100)	Dealer group name
market	VARCHAR(50)	Primary geographic market

listings

Column	Type	Description
listing_idPK	INT	Unique vehicle listing identifier
group_id	INT	Dealer group associated with the listing
vehicle_title	VARCHAR(150)	Vehicle title displayed on CarGurus
marks	INT	Listing-quality mark