Autocoder

Fractal Analytics Coding Round Questions

Last updated 2026-11-19

Analytics hiring tests usually care about whether you can wrangle a table and implement a small piece of a model correctly. This page gives twelve practice problems in that style: SQL cohorts, percentages and pivots, pandas transforms and resampling, NumPy implementations of a k-means step and regression metrics, a guesstimate and a pricing case, each with a worked solution and the mistakes that lose marks.

They are practice problems in the style of analytics screens, not copies of any test. Use them with Fractal Analytics interview questions, how to crack the SQL interview and how to crack the data science interview.

Languages hereSQL, Python, pandas, NumPy
Also coveredA guesstimate and a pricing case
LevelEasy to hard

How to approach these problems

Decide the grain of the output first. In SQL, count rows after each join to catch fan-out. In pandas, prefer vectorised methods such as groupby, transform and merge to loops. For the from-scratch problems, test on a tiny example you can verify by hand.

Hiring status

We hold no sourced facts about Fractal Analytics tests, drives, openings or cutoffs, so none are stated. Use the company's own careers site for current information.

Fractal Analytics Coding Round Questions: questions with worked answers

  1. 1

    SQL: for each monthly cohort of first-time buyers, how many returned in the following month?

    medium

    Find each customer's first order month (the cohort), take the distinct months in which each customer ordered, and count customers whose activity falls exactly one month after the cohort. COUNT(DISTINCT CASE ...) counts each returner once. Mistake: counting orders instead of customers, and missing the DISTINCT on month, which double counts customers with many orders.

    sql
    WITH firsts AS (
      SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort
      FROM orders GROUP BY customer_id
    ), months AS (
      SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS mth FROM orders
    )
    SELECT f.cohort,
           COUNT(DISTINCT f.customer_id) AS cohort_size,
           COUNT(DISTINCT CASE WHEN m.mth = f.cohort + INTERVAL '1 month'
                               THEN m.customer_id END) AS returned_next_month
    FROM firsts f
    JOIN months m ON m.customer_id = f.customer_id
    GROUP BY f.cohort
    ORDER BY f.cohort;
  2. 2

    SQL: each category's share of total revenue as a percentage.

    easy

    Aggregate by category and divide by the grand total, which a window function over the aggregated rows provides: SUM(SUM(amount)) OVER (). Multiply by 100.0 to avoid integer division. Mistake: a second subquery for the total joined with the wrong grain, or integer division returning zero.

    sql
    SELECT category,
           SUM(amount) AS revenue,
           ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS pct_of_total
    FROM sales
    GROUP BY category
    ORDER BY revenue DESC;
  3. 3

    SQL: pivot quarterly revenue per product into columns Q1 to Q4.

    medium

    Conditional aggregation: one SUM(CASE WHEN ...) per quarter, grouped by product. It works in every dialect without a PIVOT keyword. Mistake: forgetting ELSE 0 or NULL semantics; SUM ignores NULL, so missing quarters return NULL unless wrapped in COALESCE.

    sql
    SELECT product,
           COALESCE(SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN amount END), 0) AS q1,
           COALESCE(SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 2 THEN amount END), 0) AS q2,
           COALESCE(SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 3 THEN amount END), 0) AS q3,
           COALESCE(SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 4 THEN amount END), 0) AS q4
    FROM sales
    WHERE EXTRACT(YEAR FROM sale_date) = 2026
    GROUP BY product;
  4. 4

    pandas: add a z-score of sales within each store.

    medium

    groupby with transform returns a result aligned to the original rows, so you can compute (x - group mean) / group standard deviation per row. Decide ddof: pandas std defaults to the sample standard deviation (ddof=1); use ddof=0 for the population version. Mistake: using apply and then misaligned indexes, or dividing by zero for stores with constant sales.

    python
    import pandas as pd
    
    df = pd.DataFrame({"store": ["a", "a", "a", "b", "b"], "sales": [10, 20, 30, 5, 15]})
    g = df.groupby("store")["sales"]
    df["z"] = (df["sales"] - g.transform("mean")) / g.transform("std")
    print(df)
  5. 5

    pandas: monthly total sales from daily data.

    medium

    Convert the date column to datetime, set it as the index and resample with "MS" (month start) and sum. Resample includes months with no rows as zero, which is usually what you want for a continuous series. Mistake: leaving dates as strings, so the index is not a DatetimeIndex and resample raises an error.

    python
    import pandas as pd
    
    df = pd.DataFrame({"date": ["2026-01-05", "2026-01-20", "2026-03-02"],
                       "amount": [100, 50, 70]})
    df["date"] = pd.to_datetime(df["date"])
    print(df.set_index("date")["amount"].resample("MS").sum())
  6. 6

    pandas: a pivot table of average order value by region (rows) and channel (columns).

    medium

    pivot_table with index, columns, values and aggfunc="mean"; pass fill_value=0 for combinations that never occur and margins=True for totals. Mistake: using pivot instead, which fails when duplicate index and column pairs exist, since it does not aggregate.

    python
    import pandas as pd
    
    df = pd.DataFrame({"region": ["N", "N", "S", "S"], "channel": ["web", "app", "web", "web"],
                       "value": [100, 80, 60, 40]})
    print(df.pivot_table(index="region", columns="channel", values="value",
                         aggfunc="mean", fill_value=0))
  7. 7

    NumPy: one iteration of k-means, returning labels and updated centres.

    hard

    Compute the distance from every point to every centre by broadcasting, assign each point to the nearest centre with argmin, then replace each centre with the mean of its points, keeping the old centre if a cluster is empty. Time is O(n k d) per iteration. Mistake: a Python double loop over points and centres, and taking the mean of an empty cluster, which produces NaN.

    python
    import numpy as np
    
    def kmeans_step(X, centers):
        d = np.linalg.norm(X[:, None, :] - centers[None, :, :], axis=2)
        labels = d.argmin(axis=1)
        new = np.array([X[labels == k].mean(axis=0) if (labels == k).any() else centers[k]
                        for k in range(len(centers))])
        return labels, new
    
    X = np.array([[0, 0], [0, 1], [10, 10], [10, 11]], dtype=float)
    labels, new = kmeans_step(X, np.array([[0, 0], [10, 10]], dtype=float))
    print(labels, new)   # [0 0 1 1] [[ 0.   0.5] [10.  10.5]]
  8. 8

    Python: compute RMSE and R-squared from scratch.

    medium

    RMSE is the square root of the mean squared error. R-squared is 1 minus the ratio of the residual sum of squares to the total sum of squares around the mean. For y = [3, 5, 7] and predictions [2.5, 5, 7.5]: the residual sum is 0.5, the total sum is 8, so R-squared = 0.9375 and RMSE is about 0.408. Mistake: using the sum instead of the mean for RMSE, and forgetting that R-squared can be negative for a model worse than the mean.

    python
    def metrics(y, p):
        n = len(y)
        mean = sum(y) / n
        ss_res = sum((a - b) ** 2 for a, b in zip(y, p))
        ss_tot = sum((a - mean) ** 2 for a in y)
        return (ss_res / n) ** 0.5, 1 - ss_res / ss_tot
    
    print(metrics([3, 5, 7], [2.5, 5, 7.5]))  # (0.408..., 0.9375)
  9. 9

    Python: implement the sigmoid and log-loss for a binary classifier, avoiding log(0).

    medium

    The sigmoid is 1 / (1 + e^-z); log-loss is the negative mean of y log p + (1 - y) log(1 - p). Clip probabilities away from 0 and 1 by a tiny epsilon so the logarithm is defined. Mistake: no clipping, which returns infinity or NaN when a model is confidently wrong.

    python
    import math
    
    def sigmoid(z):
        return 1 / (1 + math.exp(-z))
    
    def log_loss(y, p, eps=1e-15):
        total = 0.0
        for yi, pi in zip(y, p):
            pi = min(max(pi, eps), 1 - eps)
            total += yi * math.log(pi) + (1 - yi) * math.log(1 - pi)
        return -total / len(y)
    
    print(round(log_loss([1, 0], [0.9, 0.2]), 4))  # 0.1643
  10. 10

    Pricing case: a product sells 1,000 units at Rs 100 with a unit cost of Rs 60. Price elasticity is estimated at -1.5. Should you raise the price by 10 percent?

    hard

    A 10 percent rise with elasticity -1.5 cuts volume by about 15 percent, to 850 units. Revenue becomes 850 x 110 = Rs 93,500 against Rs 1,00,000, a fall. Profit is what matters: before, (100 - 60) x 1,000 = Rs 40,000; after, (110 - 60) x 850 = Rs 42,500, an increase of about 6 percent. So raise the price on profit grounds, with caveats: elasticity estimates are uncertain, competitors may respond, and long-run effects on loyalty are not captured. Recommend a controlled price test on a subset of stores.

    python
    price, cost, units, elast = 100, 60, 1000, -1.5
    new_price = price * 1.10
    new_units = units * (1 + elast * 0.10)
    print((price - cost) * units, round((new_price - cost) * new_units, 2))  # 40000 42500.0
  11. 11

    Guesstimate: the number of two-wheeler insurance renewals per day in a state of 50 million people.

    medium

    Assume 0.25 two-wheelers per person: 12.5 million vehicles. Assume about 80 percent are insured (the rest uninsured or lapsed): 10 million policies. Policies are mostly annual, so about 10 million renewals a year, or roughly 27,000 per day on average. Seasonality matters: registrations cluster in festive months, so peaks may be a third higher. State which input to check first: the insured share.

  12. 12

    Python: given customer purchase dates, find the average gap in days between consecutive purchases per customer.

    medium

    Sort by customer and date, take the difference of consecutive dates within each customer with groupby and diff, then average the gaps per customer. Customers with one purchase have no gap (NaN) and are dropped from the mean automatically. Mistake: not sorting first, or computing gaps across customers by diffing the whole column.

    python
    import pandas as pd
    
    df = pd.DataFrame({
        "cust": [1, 1, 1, 2],
        "date": pd.to_datetime(["2026-01-01", "2026-01-11", "2026-02-10", "2026-01-05"]),
    })
    df = df.sort_values(["cust", "date"])
    df["gap"] = df.groupby("cust")["date"].diff().dt.days
    print(df.groupby("cust")["gap"].mean())  # cust 1: 20.0, cust 2: NaN

Frequently asked questions

What coding skills matter for analytics roles like Fractal?

SQL (joins, aggregation, window functions), pandas manipulation, basic NumPy and Python, and the ability to implement simple metrics and algorithms from scratch.

Do I need to implement algorithms from scratch?

Small pieces such as RMSE, a k-means step or logistic loss are plausible. Understanding what each line does matters more than memorising code.

Which SQL dialect?

Use the dialect offered in the test, often PostgreSQL or MySQL. Know how to truncate dates and extract parts in yours.

Are these actual Fractal Analytics questions?

No, they are practice problems in the style of analytics screens, not copies of any test.

How should I practise cases?

Work through pricing, forecasting, churn and marketing-mix problems with real arithmetic, and always end with a recommendation and its uncertainty.

Related guides

More in Company Interview Questions

See all in this section