SQL and PySpark Basics | Dataford Interview Questions

Explain how you would use SQL and PySpark to transform raw data into an analysis-ready dataset.

Use the supplied raw event and customer tables. Prepare valid, deduplicated customer activity metrics, excluding inactive customers, malformed contact values, unsupported event types, and unmatched customer records. The SQL result should represent the type of analysis-ready output you would also produce with PySpark.

Output

One row per active customer and activity date
Columns: customer_id, customer_name, activity_date, total_events, transaction_events, net_revenue
Include only dates with valid events, ordered by customer_id, then activity_date

Schema

raw_events

Column	Type	Description
event_id	INT	Business identifier for an event, which may be duplicated across ingestions
customer_id	INT	Customer associated with the event
email	VARCHAR(255)	Raw customer email value
event_type	VARCHAR(30)	Raw event category
event_timestamp	TIMESTAMP	Time when the event occurred
amount	NUMERIC(12,2)	Event amount
ingested_at	TIMESTAMP	Time when the record was ingested
source_system	VARCHAR(40)	System that supplied the raw record

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(120)	Customer display name
is_active	BOOLEAN	Whether the customer is currently active
segment	VARCHAR(40)	Customer segment

Your question is SQL and PySpark Basics. 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