Autocoder

LatentView Coding Round Questions

Last updated 2026-11-19

Analytics services tests usually put SQL first, followed by data-handling Python and a few classic problems. This page gives twelve practice problems in that spirit: SQL for ranking, gaps, medians and deduplication, Python for cleaning and validating records, pandas merges and a couple of algorithmic warm-ups, each with a worked solution, complexity where relevant and common mistakes.

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

Languages hereSQL (PostgreSQL style), Python, pandas
ThemesWindow functions, data cleaning, joins, validation
LevelEasy to hard

How to approach these problems

Read the table description for the grain and the keys, write the query in layers (a CTE per idea), and check row counts after each join. In Python, handle empty and malformed input explicitly and prefer small pure functions you can test.

Hiring status

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

LatentView Coding Round Questions: questions with worked answers

  1. 1

    SQL: employees who earn more than their managers.

    easy

    Self-join the table on the manager id and compare salaries. Use an inner join so employees without managers are excluded. Mistake: joining on the wrong side (matching manager to manager), which swaps the comparison.

    sql
    SELECT e.name AS employee
    FROM employees e
    JOIN employees m ON e.manager_id = m.id
    WHERE e.salary > m.salary;
  2. 2

    SQL: the median salary per department.

    medium

    PostgreSQL has an ordered-set aggregate: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) interpolates between the two middle values for even counts. Portable alternative: rank rows with ROW_NUMBER ascending and descending and average the rows where the two ranks are within one of each other. Mistake: using AVG, which is the mean, and forgetting that the median for an even count is the average of two values.

    sql
    SELECT department,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
    FROM employees
    GROUP BY department;
  3. 3

    SQL: delete duplicate customer rows, keeping the earliest id per email.

    medium

    Delete rows whose id is not the minimum for their email. Write the SELECT first to check what will be deleted. In MySQL wrap the subquery in a derived table because you cannot select from the table being modified directly. Mistake: deleting without a key to decide which duplicate survives.

    sql
    DELETE FROM customers c
    USING customers d
    WHERE c.email = d.email AND c.id > d.id;
  4. 4

    SQL: the number of days between each customer's consecutive orders.

    medium

    Use LAG over the customer partition ordered by date to fetch the previous order date, then subtract. The first order of each customer has no previous date, so the gap is NULL. Mistake: using a self-join with a "less than" condition, which pairs each order with all earlier ones rather than only the previous one.

    sql
    SELECT customer_id, order_date,
           order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_prev
    FROM orders;
  5. 5

    SQL: year-over-year growth of revenue by month.

    medium

    Aggregate by month, then LAG with an offset of 12 to fetch the same month last year; this assumes no missing months, so generate a calendar or join the previous year explicitly when gaps are possible. Guard the division with NULLIF. Mistake: comparing with the previous row, which gives month-over-month growth.

    sql
    WITH m AS (
      SELECT DATE_TRUNC('month', order_date) AS mth, SUM(amount) AS revenue
      FROM orders GROUP BY 1
    )
    SELECT mth, revenue,
           ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY mth))
                 / NULLIF(LAG(revenue, 12) OVER (ORDER BY mth), 0), 1) AS yoy_pct
    FROM m;
  6. 6

    SQL: find the missing ids in a sequence of invoice numbers.

    medium

    Generate the full range with generate_series and anti-join it against the table; unmatched numbers are missing. In dialects without it use a recursive CTE or a numbers table. Mistake: comparing with consecutive differences only, which finds gaps but not their members, unless you expand them.

    sql
    SELECT g.n AS missing_id
    FROM generate_series((SELECT MIN(invoice_id) FROM invoices),
                         (SELECT MAX(invoice_id) FROM invoices)) AS g(n)
    LEFT JOIN invoices i ON i.invoice_id = g.n
    WHERE i.invoice_id IS NULL;
  7. 7

    Python: normalise Indian mobile numbers from messy strings (spaces, dashes, +91 or 0 prefix) and flag invalid ones.

    medium

    Strip every non-digit, then drop a leading 91 or 0 when the length shows a prefix, and require exactly ten digits starting with 6 to 9. Return the cleaned number or None. O(length). Mistake: removing a leading 91 from a legitimate ten-digit number that happens to start with 91; test the length before stripping.

    python
    import re
    
    def clean_mobile(raw):
        d = re.sub(r"\D", "", raw)
        if len(d) == 12 and d.startswith("91"):
            d = d[2:]
        elif len(d) == 11 and d.startswith("0"):
            d = d[1:]
        return d if re.fullmatch(r"[6-9]\d{9}", d) else None
    
    print(clean_mobile("+91 98765-43210"), clean_mobile("12345"))  # 9876543210 None
  8. 8

    Python: find the missing dates in a list of daily records.

    medium

    Build the set of expected dates between the minimum and maximum and subtract the observed ones. O(n + range). In pandas, date_range plus difference does the same. Mistake: assuming the list is sorted or has no duplicates; use a set.

    python
    from datetime import date, timedelta
    
    def missing_dates(dates):
        seen = set(dates)
        lo, hi = min(seen), max(seen)
        return [lo + timedelta(days=i) for i in range((hi - lo).days + 1)
                if lo + timedelta(days=i) not in seen]
    
    print(missing_dates([date(2026, 1, 1), date(2026, 1, 4)]))  # Jan 2 and Jan 3
  9. 9

    pandas: join orders and customers, then report the number of orders and total spend per city, including customers with no orders.

    medium

    Start from customers and left merge orders so zero-order customers survive, then group by city; use nunique on order ids so unmatched rows count zero, and sum with fillna. Mistake: starting from orders, which drops customers with no orders, and counting rows with size, which counts the unmatched customer row as one order.

    python
    import pandas as pd
    
    cust = pd.DataFrame({"cid": [1, 2, 3], "city": ["A", "A", "B"]})
    orders = pd.DataFrame({"oid": [10, 11], "cid": [1, 1], "amt": [100, 50]})
    m = cust.merge(orders, on="cid", how="left")
    out = m.groupby("city").agg(orders=("oid", "nunique"), spend=("amt", "sum"))
    print(out)   # A: 2 orders, 150; B: 0 orders, 0
  10. 10

    Python: flatten an arbitrarily nested list.

    medium

    Recurse: for each element, if it is a list, extend with its flattened form, otherwise append it. O(total elements). For very deep nesting, use an explicit stack to avoid the recursion limit. Mistake: flattening strings, since a string is iterable; check for list instead of iterability.

    python
    def flatten(x):
        out = []
        for item in x:
            if isinstance(item, list):
                out.extend(flatten(item))
            else:
                out.append(item)
        return out
    
    print(flatten([1, [2, [3, 4]], 5]))  # [1, 2, 3, 4, 5]
  11. 11

    Python: validate a data feed: report rows with a missing key, a duplicate key or a negative amount, with line numbers.

    hard

    One pass with a dictionary of seen keys: record the first line of each key, and when it reappears flag a duplicate with both line numbers; separately flag a missing key and a negative amount. O(n). In practice return structured issues so they can be written to a quality report. Mistake: stopping at the first error, which hides the rest, and using a list for the seen keys, which makes it O(n squared).

    python
    def validate(rows):
        seen, issues = {}, []
        for line, (key, amount) in enumerate(rows, start=1):
            if not key:
                issues.append((line, "missing key"))
            elif key in seen:
                issues.append((line, f"duplicate of line {seen[key]}"))
            else:
                seen[key] = line
            if amount < 0:
                issues.append((line, "negative amount"))
        return issues
    
    print(validate([("a", 10), ("b", -5), ("a", 3), ("", 1)]))
  12. 12

    SQL: find users whose spend in the latest month is at least double their average of the previous three months.

    hard

    Compute monthly spend per user, then use AVG as a window over the three preceding rows (ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), and compare in the final month only. This requires one row per user-month, so fill gaps if a user skipped months. Mistake: including the current month in the frame, which dilutes the baseline and misses real spikes.

    sql
    WITH m AS (
      SELECT user_id, DATE_TRUNC('month', paid_at) AS mth, SUM(amount) AS spend
      FROM payments GROUP BY 1, 2
    ), w AS (
      SELECT user_id, mth, spend,
             AVG(spend) OVER (PARTITION BY user_id ORDER BY mth
                              ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS prev3
      FROM m
    )
    SELECT user_id, spend, prev3
    FROM w
    WHERE mth = (SELECT MAX(mth) FROM m) AND prev3 IS NOT NULL AND spend >= 2 * prev3;

Frequently asked questions

What matters most in an analytics services coding test?

SQL fluency first (joins, windows, aggregation), then data-handling Python with pandas, and finally a few classic algorithm warm-ups.

Which SQL dialect should I practise?

Practise PostgreSQL syntax and know the differences in MySQL and SQL Server for dates, string functions and window frames.

Are these real LatentView questions?

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

How should I debug a wrong SQL result?

Check the grain, count rows after each join, look for NULLs in join keys and test the CTEs one at a time.

Do I need Spark?

It depends on the track. For data engineering roles, learn DataFrame operations and shuffles; for analyst roles SQL and pandas usually come first.

Related guides

More in Company Interview Questions

See all in this section