Lyft Data Engineer Interview Guide

Schema: drivers(driver_id INT, name VARCHAR), rides(ride_id INT, rider_id INT, driver_id INT, start_time TIMESTAMP, end_time TIMESTAMP, pick_up VARCHAR, drop_off VARCHAR, cost DECIMAL). Expected approach: JOIN drivers to rides ON driver_id, GROUP BY driver_id and name, compute COUNT(*) AS total_rides, AVG(cost) AS average_cost, and SUM(CASE WHEN cost > 20 THEN 1 ELSE 0 END) AS rides_over_20. DataLemur explicitly labels this as a Data Engineer question at Lyft.

Schema: orders(order_id, customer_id, order_date,...). Approach: find each customer's MIN(order_date) as their first order date, then group by that date to count new customers per day. Alternative approach: LEFT JOIN customers against all prior orders and filter for NULL matches. Requires understanding of MIN aggregate with GROUP BY and self-join or window function alternatives. Lyft DE phone screen or onsite SQL question.

Schema: purchases(customer_id, purchase_date,...). Approach 1: Self-join on customer_id where DATEDIFF(p2.purchase_date, p1.purchase_date) = 1. Approach 2: use LAG() window function to get previous purchase date per customer, then filter for date difference of 1 day. Need to handle duplicates (multiple purchases per day) by first deduplicating on (customer_id, DATE(purchase_date)). Edge cases: same-day multiple purchases, timezone differences. Lyft DE interview.

Given a sorted list of unique integers and an integer n, return the nth number not present in the list. Iterate through the sorted list tracking gaps between consecutive elements, counting missing numbers until the nth is found. Tests loop control, arithmetic, and edge case handling.