Katkat
Back

Solve SQL and PySpark Data Tasks

Point72Data Engineer · Online Assessment
EasyData EngineerOnline AssessmentCoding & Algorithms

Last updated

This question tests your ability to handle data cleaning, filtering, and aggregation using both SQL and PySpark syntax on a transaction dataset.

Scenario

You are given a dataset containing financial transactions. Each row represents a trade with the following columns: trade_id (string), account_id (string), amount (float), trade_date (timestamp), and status (string: 'COMPLETED', 'PENDING', 'FAILED').

Tasks

  1. Write a SQL query to calculate the total transaction amount per account_id for 'COMPLETED' trades, filtered to include only transactions that occurred in the year 2023. Order the results by total amount in descending order.

  2. Using PySpark, perform the same calculation as above. Additionally, remove any duplicate trade_id entries before performing the aggregation. Ensure the result is returned as a Spark DataFrame.

Data Assumptions

  • Input: trades table.
  • Constraints: The dataset can contain duplicate trade_id entries that must be handled in the PySpark version.

Example

Input trades table:

trade_idaccount_idamounttrade_datestatus
1A1100.02023-01-15COMPLETED
1A1100.02023-01-15COMPLETED
2A150.02023-02-10COMPLETED
3A2200.02022-12-31COMPLETED

Output:

account_idtotal_amount
A1150.0

Explanation: Duplicate trade_id 1 is removed. Trade 3 is ignored due to the date filter (2022).

Hints

Hint 1

In SQL, use the YEAR() function or a date range filter on the trade_date column.

Hint 2

In PySpark, use .dropDuplicates(['trade_id']) to clean the data before applying .groupBy().

Hint 3

Remember that PySpark aggregation functions like sum() must be imported from pyspark.sql.functions.

Follow-up questions

  • How would you handle missing values (NULLs) in the 'amount' column before performing the aggregation?
  • If the 'trades' table were partitioned by 'trade_date', how would that change your query strategy for better performance?
  • How does a broadcast join work in Spark, and in what scenario would you suggest using it?