Data Engineer Phone Interview.

Firstly, the recruiter asks 5 basic Python questions and 5 SQL questions. Then, there is a 1-hour phone interview:

• 24 minutes for Python
• 25 minutes for SQL

It looked easy, mostly Python and SQL, based on Glassdoor.

Python

1. Returns the number of times a given character occurs in the given string. python s1 = 'missisipi' res = []

for i in range(len(s1)): if s1[i] == 's': res.append('s') print(len(res))

1. Fills None values in a list with the preceding non-None value. python arr = [None, 1, 2, None] new_l = [] for i in range(len(arr)): if arr[i] is not None: new_l.append(arr[i]) else: # Assumes there's always a preceding element if the current one is None new_l.append(arr[i-1]) print(new_l)

2. Given two sentences, construct an array that has the words that appear in one sentence and not the other. python A = "Geeks for Geeks" B = "Learning from Geeks for Geeks" d = {}

for w in A.split(): d[w] = d.get(w, 0) + 1 for w in B.split(): d[w] = d.get(w, 0) + 1

unmatchedW = [w for w in d if d[w] == 1] print(unmatchedW)

1. Sorts a dictionary by its values in descending order. python d = {"a": 4, "c": 3, "b": 12} print([(k, v) for k, v in sorted(d.items(), key=lambda x: x[1], reverse=True)])

Expected Output: [('b', 12), ('a', 4), ('c', 3)]

SQL

(Schema provided implicitly)

1. Find the top 5 sales products that had promotions. sql SELECT SUM(s.store_sales) AS total_sales, p.brand_name, COUNT(p.product_id) AS product_count FROM products p JOIN sales s ON p.product_id = s.product_id WHERE s.promotion_id IS NOT NULL GROUP BY p.brand_name HAVING COUNT(p.product_id) = 1 -- This condition seems specific, may need clarification ORDER BY total_sales DESC LIMIT 5;

2. Calculate the percentage of sales that occurred on either the first or last day of a promotion campaign.

Option 1 (Using First_day and last_day functions - assumes these are defined or can be simulated): sql SELECT SUM(CASE WHEN valid_promotion = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) FROM sales WHERE -- Assuming 'day' refers to the day of the month and promotion start/end dates are accessible day = First_day(date) OR day = last_day(date);

Option 2 (Directly comparing dates if transaction_date is available): sql SELECT SUM(CASE WHEN transaction_date = (SELECT MIN(transaction_date) FROM sales) THEN 1 ELSE 0 END) * 100.0 / COUNT() AS first_day_sales_percentage, SUM(CASE WHEN transaction_date = (SELECT MAX(transaction_date) FROM sales) THEN 1 ELSE 0 END) * 100.0 / COUNT() AS last_day_sales_percentage FROM sales;

Option 3 (Joining with promotions table): sql SELECT AVG(CASE WHEN s.transaction_date IN (p.start_date, p.end_date) THEN 1.0 ELSE 0.0 END) * 100 AS first_last_pct FROM sales s JOIN promotions p ON s.promotion_id = p.promotion_id;