Read Queries and Max User | Dataford Interview Questions

Read queries from a text file and give maximum queried user — in python and spark

Asked in the technical round 2 stage. The file is audit.txt, with columns userid, timestamp, tablename, and queries. Use the SQL representation below to return all users tied for the highest number of recorded queries.

Output

One row per user tied for the maximum query count
Columns: userid, query_count
Include users with no audit records when calculating the maximum, and exclude records with no user ID
Order by userid ascending

Schema

users

Column	Type	Description
useridPK	INT	Unique identifier for a user

audit_queries

Column	Type	Description
audit_idPK	INT	Unique identifier for an audit record
userid	INT	User who issued the query
timestamp	TIMESTAMPTZ	Time when the query was recorded
tablename	VARCHAR(100)	Table referenced by the query
queries	TEXT	Query text captured from audit.txt

Your question is Read Queries and Max User. 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