Optimize Large-Scale Event Processing

PYSPARK coding challenge · Difficulty: medium · Topic: Aggregation · +150 XP

Problem

vehicle_events holds millions of raw events from an Uber-like platform. It is a wide table -- ten columns, including a fat device_info string -- but the report you have been asked for needs a narrow slice of it.

Produce, for January 2024 trips only, the number of events and the total distance per vehicle per day.

Write it the way you would on a real cluster: cut the data down before you do anything expensive with it.

Input DataFrame — `vehicle_events`

| Column | Type | Description |
| --- | --- | --- |
| event_id | LONG | Unique event id |
| vehicle_id | STRING | Vehicle identifier |
| event_time | TIMESTAMP | Event timestamp |
| event_type | STRING | `TRIP`, `CANCEL`, `IDLE`, ... |
| distance_km | DOUBLE | Distance for the event |
| latitude | DOUBLE | Vehicle latitude |
| longitude | DOUBLE | Vehicle longitude |
| driver_id | STRING | Driver identifier |
| city | STRING | City |
| device_info | STRING | Device string |

Rules

• Keep only events in January 2024 -- from 2024-01-01 00:00:00 up to and including 2024-01-31 23:59:59. Nothing from December or February.
• Keep only rows where event_type = 'TRIP'. A CANCEL or an IDLE is not a trip, even in January.
• event_date is the calendar date of event_time.
• event_count counts the qualifying events; total_distance_km sums their distance_km.
• Sort by vehicle_id ASC, then event_date ASC.

Example Input

| event_id | vehicle_id | event_time | event_type | distance_km | city |
| --- | --- | --- | --- | --- | --- |
| 101 | V101 | 2024-01-05 08:15:00 | TRIP | 12.5 | Delhi |
| 102 | V101 | 2024-01-05 10:30:00 | TRIP | 8.0 | Delhi |
| 103 | V101 | 2024-02-01 09:00:00 | TRIP | 15.0 | Delhi |
| 104 | V102 | 2024-01-10 12:00:00 | TRIP | 20.0 | Mumbai |
| 105 | V102 | 2024-01-15 14:00:00 | TRIP | 10.0 | Mumbai |
| 106 | V101 | 2024-01-05 11:00:00 | CANCEL | 5.0 | Delhi |
| 107 | V102 | 2024-01-10 12:30:00 | IDLE | 0.0 | Mumbai |
| 108 | V103 | 2023-12-31 23:59:59 | TRIP | 9.0 | Pune |
| 109 | V103 | 2024-01-01 00:00:00 | TRIP | 7.0 | Pune |
| 110 | V103 | 2024-01-31 23:59:59 | TRIP | 3.5 | Pune |
| 111 | V103 | 2024-02-01 00:00:00 | TRIP | 11.0 | Pune |
| 112 | V104 | 2024-01-20 23:50:00 | TRIP | 4.0 | Bengaluru |
| 113 | V104 | 2024-01-21 00:10:00 | TRIP | 6.0 | Bengaluru |
| 114 | V105 | 2023-01-20 09:00:00 | TRIP | 25.0 | Kolkata |

(latitude, longitude, driver_id and device_info are present too -- they are just not shown here, and you do not need them.)

Expected Output

| vehicle_id | event_date | event_count | total_distance_km |
| --- | --- | --- | --- |
| V101 | 2024-01-05 | 2 | 20.5 |
| V102 | 2024-01-10 | 1 | 20.0 |
| V102 | 2024-01-15 | 1 | 10.0 |
| V103 | 2024-01-01 | 1 | 7.0 |
| V103 | 2024-01-31 | 1 | 3.5 |
| V104 | 2024-01-20 | 1 | 4.0 |
| V104 | 2024-01-21 | 1 | 6.0 |

Explanation

V101 has three January-dated rows, but 106 is a CANCEL and 103 is

February. Only 101 and 102 survive: 2 events, 12.5 + 8.0 = 20.5.

V102 on 2024-01-10 has a TRIP and an IDLE. The IDLE is dropped, so

the count is 1, not 2.

V103 is the boundary. 2023-12-31 23:59:59 is out, 2024-01-01 00:00:00 is

in, 2024-01-31 23:59:59 is in, 2024-02-01 00:00:00 is out. Two rows survive,

on two different days.

V104 crosses midnight -- 23:50 and 00:10 are twenty minutes apart but

land on different event_date values.

V105 is January -- of the wrong year. 2023-01-20 is a January date, so a

filter written as F.month(event_time) == 1 keeps it. The requirement is January

2024, so V105 must not appear in the output at all.

Task

Build df_result that:

1. Filters first -- January 2024 and event_type = 'TRIP'

2. Selects only the columns the answer needs

3. Derives event_date from event_time

4. Groups by vehicle_id, event_date

5. Aggregates event_count and total_distance_km

6. Orders by vehicle_id ASC, event_date ASC

7. Ends with df_result.show()

Constraints

• PySpark DataFrame API only -- no spark.sql(), no SQL strings
• Do not collect() to the driver, and do not aggregate in a Python loop
• vehicle_events is provided -- do not recreate it

Notes

• Return exactly: vehicle_id, event_date, event_count, total_distance_km
• Functions to use: filter, select, F.to_date, groupBy, F.count, F.sum

What this PYSPARK challenge teaches you

“Optimize Large-Scale Event Processing” is a medium-level PYSPARK challenge focused on Aggregation. Working through it gives you hands-on practice with filter, predicate pushdown, column pruning, groupBy, optimization, to_date — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

• filter
• predicate pushdown
• column pruning
• groupBy
• optimization
• to_date

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

1. Do the filtering before anything else. Every row you drop early is a row that never gets shuffled, and the shuffle is the expensive part of a groupBy.
2. Two conditions, not one: the month AND event_type == 'TRIP'. The sample deliberately contains a CANCEL and an IDLE inside January.
3. For the month, a half-open range is safest: event_time >= '2024-01-01' AND event_time < '2024-02-01'. That takes 2024-01-31 23:59:59 and excludes 2024-02-01 00:00:00 without you having to reason about the last second of the month.
4. After filtering, .select() just the columns the answer needs — vehicle_id, event_time, distance_km. The DataFrame has ten, including a long device_info string that would otherwise be carried through the shuffle for nothing.
5. F.to_date('event_time') gives the calendar day. Group by vehicle_id and that date together.

Where this comes up

Variations of this problem have been reported in interviews at Uber. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your PySpark code in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related PYSPARK challenges

• Find Duplicate Emails
• HR: Average Salary by Department
• Logistics: Count Shipments by Status
• Count Total Orders Placed by Each Customer
• Find Average Order Amount for Each Customer
• Find Customers Who Placed More Than 5 Orders (HAVING)

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The PYSPARK environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated medium and covers Aggregation.

Solve this challenge free on PySpark.in