Question 1 -
You are given a dataset containing customer information. Some fields have NULL values.
Write code to replace NULL values with default values.
For the age column, replace NULL with the average age, and for the city column, replace NULL with 'Unknown'.
data = [
(1, "John", 25, "New York"),
(2, "Sarah", None, "San Francisco"),
(3, "Michael", 40, None),
(4, "Jessica", None, "Los Angeles"),
(5, "David", 35, "Seattle")
]
columns = ["customer_id", "name", "age", "city"]
------------------------------------------------------------------------------------------------------------------
Question 2 -
Write a code to calculate the total amount spent by each customer.
customers = [
(1, "John", 25, "New York"),
(2, "Sarah", 30, "San Francisco"),
(3, "Michael", 40, "Chicago"),
(4, "Jessica", 28, "Los Angeles"),
(5, "David", 35, "Seattle")
]
transactions = [
(1001, 1, 250.00),
(1002, 2, 300.00),
(1003, 1, 150.00),
(1004, 3, 200.00),
(1005, 4, 100.00),
(1006, 2, 50.00)
]
customer_columns = ["customer_id", "name", "age", "city"]
transaction_columns = ["transaction_id", "customer_id", "amount"]
------------------------------------------------------------------------------------------------------------------
Question 3 -
You are given a dataset with date, product_id, and sales_qty. Some rows contain null values for sales_qty. You are asked to forward-fill the missing values with the last non-null value per product_id in time order
data = [
("2024-01-01", 101, 10),
("2024-01-02", 101, None),
("2024-01-03", 101, 15),
("2024-01-01", 102, 20),
("2024-01-02", 102, None),
("2024-01-03", 102, None),
]
columns = ["date", "product_id", "sales_qty"]
------------------------------------------------------------------------------------------------------------------
Question 4 -
You are given a dataset with columns user_id, event_type, event_timestamp, and some rows contain duplicate events. A duplicate event is defined as an event having the same user_id, event_type, and event_timestamp. Write a PySpark job that removes duplicate events, but you need to keep the event with the latest occurrence for each user.
data = [
(1, "login", "2024-01-01 10:00:00"),
(1, "login", "2024-01-01 10:00:00"), # Duplicate
(2, "click", "2024-01-01 11:00:00"),
(3, "purchase", "2024-01-01 12:00:00"),
(3, "purchase", "2024-01-01 12:00:00"), # Duplicate
(3, "login", "2024-01-01 09:00:00"),
]
columns = ["user_id", "event_type", "event_timestamp"]
------------------------------------------------------------------------------------------------------------------
SQL:
Write a query to find the top 5 customers with the highest total order amounts.
Write a query to find the average salary for each department, excluding employees with salaries above 500000
Write a query to find the department with the highest average salary for employees who have been with the company for more than 2 years.