Top Visited Websites Ordering | Dataford Interview Questions

The Nio Robotics Fleet Console records websites visited by robotics operators. Write a PostgreSQL query that identifies the three most visited websites based on human visits.

Requirements

Exclude visits where is_bot is TRUE.
Return each website's domain, total human visit count, and distinct visitor count.
Sort by total visits descending, then website_id ascending for deterministic tie handling, and return only the top three websites.
Use the websites and page_visits tables. Websites with no qualifying visits should remain eligible for ranking with zero counts.

Schema

websites

Column	Type	Description
website_idPK	INTEGER	Unique website identifier
domain	VARCHAR(255)	Website domain
category	VARCHAR(80)	Website category

page_visits

Column	Type	Description
visit_idPK	INTEGER	Unique visit identifier
website_id	INTEGER	Visited website identifier
visitor_id	INTEGER	Operator identifier, null for anonymous visits
visited_at	TIMESTAMP	Timestamp of the visit
is_bot	BOOLEAN	Whether the visit was generated by a bot

Your question is Top Visited Websites Ordering. 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