Data analysis in Python

1.Orders, revenue, and average by channel

Marketing wants a scorecard per acquisition channel. Write summarize_by_channel(orders), where each order is {"channel": str, "amount": number}. Return a dict mapping each channel to {"orders": count, "revenue": sum of amount, "avg": revenue / orders rounded to 2 decimals}. Return {} for no orders.

2.Top N products per category

Merchandising wants the best sellers in each category. Write top_products(sales, n), where each row is {"category": str, "product": str, "units": int} and a product can appear in many rows. Sum units per product within its category. Return a dict mapping each category to a list of at most n product names, by total units descending, ties broken by product name ascending.

3.Pivot revenue by region and month

Finance wants a region by month table. Write pivot_revenue(rows), where each row is {"region": str, "month": "YYYY-MM", "revenue": number}. Return {region: {month: total revenue}}. Every region must have an entry for every month that appears anywhere in the data, using 0 where that region had no rows. Return {} for no rows.

4.Percent change period over period

Write pct_change(values) for a list of period totals in time order. Return a list of the same length: the first entry is None, and each later entry is (current - previous) / previous * 100 rounded to 2 decimals, or None when the previous value is 0. Return [] for an empty list.

5.Month-over-month growth from daily rows

Write monthly_growth(rows), where each row is {"date": "YYYY-MM-DD", "revenue": number}. Sum revenue per month. Return a list of {"month": "YYYY-MM", "revenue": total, "growth": g}, one per month with rows, sorted by month. g = (total - prior) / prior rounded to 4 decimals, where prior is the previous calendar month's total; g is None if the previous calendar month has no rows or totals 0.

6.7-day moving average

Ops wants a smoothed daily signups line. Write moving_average_7(daily), where each row is {"date": "YYYY-MM-DD", "value": number}, one row per consecutive day but possibly unsorted. Sort by date. For each date from the 7th onward, return {"date": date, "avg": mean of that day and the 6 before, rounded to 2 decimals}, in date order. Fewer than 7 rows gives [].

7.Products that make 80% of revenue

Write pareto_products(revenue, share), where revenue maps product name to total revenue (>= 0) and share is a fraction like 0.8. Take products by revenue descending, ties by name ascending, and return the shortest prefix of names whose cumulative revenue is >= share times total revenue. Return [] if the dict is empty or total revenue is 0.

8.Retention by signup month

Growth wants month-1 retention per signup cohort. Write retention(users, activity): users are {"user": str, "signup_month": "YYYY-MM"}, activity rows are {"user": str, "month": "YYYY-MM"}. For each signup month, return the fraction of that cohort with any activity in the next calendar month, rounded to 4 decimals. Ignore activity from users not in users. Return {cohort: rate}.

9.Conversion funnel step rates

Write funnel_rates(events, steps): events are {"user": str, "step": str}, steps is the ordered step list. A user counts at step i only if they have events for steps 0..i (event order ignored, repeats count once). Return one {"step", "users", "rate"} per step. rate: users / previous step's users to 4 decimals, None if the previous step has 0 users; step 0's rate is 1.0, or None if it has 0 users.

10.First purchase per customer

CRM wants each customer's first order. Write first_purchases(orders), where each order is {"customer": str, "date": "YYYY-MM-DD", "order_id": int}, in any order. Return {customer: order_id of their earliest order}. If a customer has several orders on that earliest date, use the smallest order_id. Return {} for no orders.

11.Repeat purchase rate

Write repeat_purchase_rate(lines), where each row is an order line {"customer": str, "order_id": int}; one order can have many lines. A repeat customer has 2 or more distinct order_ids. Return repeat customers divided by all customers, rounded to 4 decimals, or 0.0 if there are no lines.

12.Average order value by segment

Write aov_by_segment(lines, customers): lines are {"order_id": int, "customer": str, "amount": number} (an order can span lines); customers are {"customer": str, "segment": str}. Use segment "unknown" for customers not listed. Per segment, AOV = total amount / number of distinct order_ids, rounded to 2 decimals. Return {segment: AOV} for segments with lines, or {} if none.

13.Median by group

Support wants the median resolution time per team. Write median_by_group(rows, key, value): rows are dicts, key and value name the group field and the numeric field. Return {group: median}. The median is the middle value after sorting, or the mean of the two middle values when the count is even. Return {} for no rows.

14.Nearest-rank percentile per endpoint

Write latency_percentile(requests, p) for SRE dashboards: requests are {"endpoint": str, "ms": number}, p is an integer 0 to 100. Use nearest rank: sort an endpoint's ms ascending, rank = ceil(p * n / 100), at least 1, and take the value at that 1-based rank (no interpolation). Return {endpoint: value}, or {} for no requests.

15.Bucket values into ranges

Write histogram(values, edges) for an order-size chart. edges is a strictly increasing list of at least 2 numbers. Bin i covers edges[i] <= v < edges[i+1] (right edge excluded). Return a list of len(edges) - 1 counts. Values below the first edge or at or above the last edge are not counted.

16.Left join orders to customers

Write join_orders(orders, customers): orders are {"order_id": int, "customer_id": int, "amount": number}; customers are {"customer_id": int, "name": str, "country": str} with unique ids. Return one dict per order, in input order, with the order's three fields plus "name" and "country" from its customer, both None if the customer is missing.

17.Deduplicate keeping the latest row

The CRM export has duplicate contacts. Write latest_records(rows), where each row is {"id": int, "updated_at": "YYYY-MM-DD HH:MM", "email": str}. Keep one row per id: the one with the latest updated_at; if several share that timestamp, keep the one that appears last in the input. Return the kept rows sorted by id ascending.

18.Year-over-year by month

Write yoy_by_month(rows, year): rows are {"date": "YYYY-MM-DD", "revenue": number}. For each month number with rows in year or year - 1, return {"month": "MM", "current": total in year (0 if none), "previous": total in year - 1 (0 if none), "change": (current - previous) / previous rounded to 4 decimals, None if previous is 0}, sorted by month. Ignore other years.

19.Dense rank sales reps

Sales wants a leaderboard. Write dense_rank(rows), where each row is {"rep": str, "sales": number}, one per rep. Return a list of {"rep", "sales", "rank"} sorted by sales descending, ties by rep ascending. Rank is dense: the top sales get 1, equal sales share a rank, and the next lower amount gets the next integer (1, 1, 2, not 1, 1, 3).

20.Days between first and last order

Write days_active(orders), where each order is {"customer": str, "date": "YYYY-MM-DD"}, in any order. Return {customer: number of days from their earliest to their latest order date} as an int; a customer with orders on only one date gets 0. Return {} for no orders.

21.Daily active users from sessions

Write daily_active_users(sessions), where each session is {"user": str, "start": "YYYY-MM-DD HH:MM", "end": "YYYY-MM-DD HH:MM"} and end >= start. A user is active on every calendar date from the start date through the end date, inclusive. Return {date: number of distinct active users} with only dates that have users, keys in ascending date order.

22.Churned customers as of a date

Retention wants a win-back list. Write churned_customers(orders, as_of, n): orders are {"customer": str, "date": "YYYY-MM-DD"}. Ignore orders dated after as_of. A customer is churned if as_of minus their latest remaining order date is more than n days. Customers with no orders on or before as_of are not listed. Return churned customer names sorted ascending.

23.Weighted average selling price

Pricing wants the real average price per product. Write avg_selling_price(sales), where each row is {"product": str, "price": number, "qty": int >= 0}. Return {product: sum of price * qty / sum of qty, rounded to 2 decimals}, or None for a product whose total qty is 0. Return {} for no rows.

24.Share of total per category

Write category_share(rows, decimals) for a revenue mix chart. Rows are {"category": str, "amount": number >= 0}. Return {category: 100 * category total / grand total, rounded with round(x, decimals)}. If the grand total is 0, every category gets 0.0. Return {} for no rows.

25.Flag spikes over the trailing mean

Write flag_spikes(values, window, k) for daily ticket counts. Return the indexes i (ascending) where i >= window and values[i] > k * mean(values[i - window : i]), the mean of the window values just before i (not including i). Equal to k times the mean is not a spike. Indexes before window are never flagged.

26.New vs returning customers per month

Write new_vs_returning(orders), where each order is {"customer": str, "date": "YYYY-MM-DD"}, in any order. For each month ("YYYY-MM") with orders, count distinct customers who ordered that month: "new" if it is the month of their first-ever order, else "returning". Return {month: {"new": int, "returning": int}}, keys in ascending month order.

27.Revenue by day of week

Staffing wants revenue by weekday. Write revenue_by_weekday(rows), where each row is {"date": "YYYY-MM-DD", "revenue": number}. Return a dict with exactly the keys "Monday" through "Sunday" in that order, each mapped to the total revenue on that weekday, or 0 when there is none.

28.Products bought together

Write top_pairs(lines, n) for a bundle idea. lines are {"order_id": int, "product": str}. For each order, take its distinct products and count every unordered pair once. Return the top n as [a, b, count] with a < b alphabetically, sorted by count descending, then a, then b. Orders with one product add nothing; fewer pairs than n returns them all.