Deloitte interview questions & answers

20 Deloitte interview questions with full model answers — Behavioral, Product & growth, Coding. Drawn from the same bank ChannelPulse drills from (45 Deloitte questions in total).

BehavioralMediumDeloitteData ScientistBehavioral round

1. Give an example of a time that you used data to drive business impact.

Model answer

The flow

  1. Identify the business problem and its impact.
  2. Gather and analyze relevant data.
  3. Develop a hypothesis based on the analysis.
  4. Communicate findings and recommendations.
  5. Implement the solution and measure results.

The answer

Situation: At my previous job as a Data Scientist at a retail company, we faced declining sales in one of our key product categories, which was concerning for our quarterly targets. I was tasked with understanding the root cause and proposing data-driven solutions to reverse this trend.

Task: My goal was to analyze sales data, identify patterns or anomalies, and recommend strategies to improve sales performance in the struggling category. The challenge was to provide actionable insights within a month to align with the upcoming marketing campaign.

Action:

  • Data Collection: I began by collecting historical sales data, customer demographics, and marketing spend for the product category over the past year.
  • Analysis: Using Python, I conducted exploratory data analysis (EDA) to identify trends and correlations. I discovered that sales were significantly lower in regions where marketing spend was disproportionately low.
  • Hypothesis Development: I hypothesized that increasing marketing efforts in these underperforming regions could boost sales.
  • Communication: I presented my findings to the marketing and sales teams, using visualizations to clearly illustrate the correlation between marketing spend and sales performance.
  • Recommendation: I recommended reallocating a portion of the marketing budget to focus on the underperforming regions, supported by a predictive model that projected a potential 15% increase in sales.

Result: The team implemented my recommendations, and within two months, we observed a 12% increase in sales for the product category. This not only helped us meet our quarterly targets but also improved our understanding of regional marketing dynamics. I learned the importance of cross-functional collaboration and the power of data-driven decision-making in achieving business goals.

Why this works

  • Tests analytical skills: The interviewer is assessing the candidate's ability to identify and analyze relevant data to solve a business problem.
  • Evaluates communication: A strong answer demonstrates the ability to communicate complex data insights to non-technical stakeholders effectively.
  • Measures impact: The answer highlights the tangible business impact of the candidate's analysis, which is crucial for a data-driven role.
  • Weakness in vague answers: Weak responses lack specific details on the analysis process, the data used, or the business impact, making it difficult to gauge the candidate's true contribution.
CodingEasyDeloitteSoftware Engineer

2. Roman to Integer (LeetCode)

Model answer

The task

Convert a string of Roman numeral symbols into its integer value. Roman numerals use a small set of symbols, with some smaller values placed before larger ones to indicate subtraction.

Key idea

Scan the string from left to right, comparing each symbol’s value with the next one. If the current value is smaller, subtract it; otherwise, add it. This handles subtractive pairs without needing to process them separately.

Approach

  1. Store the integer value for each Roman numeral symbol in a lookup table.
  2. Initialize a running total to zero.
  3. For each symbol, compare its value with the following symbol’s value, if one exists.
  4. Subtract the current value when it is smaller than the next value; otherwise, add it.
  5. Return the total. This assumes the input is a valid Roman numeral.
class Solution:
    def romanToInt(self, s: str) -> int:
        values = {
            "I": 1,
            "V": 5,
            "X": 10,
            "L": 50,
            "C": 100,
            "D": 500,
            "M": 1000,
        }

        total = 0

        for i, symbol in enumerate(s):
            current = values[symbol]

            # A smaller value before a larger value is subtracted.
            if i + 1 < len(s) and current < values[s[i + 1]]:
                total -= current
            else:
                total += current

        return total

Complexity: Time: O(n), where n is the string length, since each symbol is visited once.

Complexity: Space: O(1), because the lookup table has a fixed number of entries.

Common follow-ups

  • How would you handle invalid Roman numeral strings? Validate symbol order and repetition rules before or during conversion.
  • How would you convert an integer to a Roman numeral? Greedily subtract from a descending list of allowed values, including subtractive pairs.
  • Can the conversion be done scanning right to left? Yes; subtract a value if it is smaller than the largest value already seen to its right, otherwise add it.
CodingEasyDeloitteSoftware Engineer

3. Palindrome Number (LeetCode)

Model answer

The task

Determine whether an integer reads the same from left to right and right to left. Negative values are not palindromes.

Key idea

Reverse only the last half of the digits, then compare it with the remaining first half. This avoids converting to a string and keeps the extra space constant.

Approach

  1. Return False for negative integers.
  2. Return False for positive integers ending in zero; zero itself is a palindrome.
  3. Repeatedly move the last digit from the number to a reversed-half accumulator.
  4. Stop when the accumulator has at least as many digits as the remaining number.
  5. Compare the halves directly for even digit counts, or discard the middle digit for odd counts.
class Solution:
    def isPalindrome(self, x: int) -> bool:
        # Negative numbers have a leading minus sign, so they cannot be palindromes.
        if x < 0:
            return False

        # A positive number ending in zero would need a leading zero to be palindromic.
        if x != 0 and x % 10 == 0:
            return False

        reversed_half = 0

        # Move digits until the reversed half is at least as long as the remaining half.
        while x > reversed_half:
            reversed_half = reversed_half * 10 + x % 10
            x //= 10

        # For an odd number of digits, ignore the middle digit.
        return x == reversed_half or x == reversed_half // 10

Complexity:

  • Time: O(log₁₀ n), for processing the digits of the number.
  • Space: O(1), using only a few integer variables.

Common follow-ups

  • How would you check a palindrome by converting to a string? Compare symmetric characters, using O(log n) extra space.
  • How would you avoid integer overflow in a fixed-width language? Reversing only half the digits keeps the accumulator bounded.
  • What about zero and numbers ending in zero? Zero is palindromic; other numbers ending in zero are not.
CodingEasyDeloitteSoftware Engineer

4. Two Sum (LeetCode)

Model answer

The task

Given a list of integers and a target, return the indices of two distinct elements whose values add up to the target. The usual version guarantees a valid pair.

Key idea

As we scan the list, store each value’s index in a hash table. For the current value, check whether its needed complement has already appeared; this finds a pair in one pass.

Approach

  1. Create a dictionary mapping previously seen values to their indices.
  2. For each element, compute the complement needed to reach the target.
  3. If that complement is in the dictionary, return its stored index and the current index.
  4. Otherwise, store the current value and index, then continue.
from typing import List

class Solution:
    def twoSum(self, nums: List[int], target: int) -> List[int]:
        # Map each value already visited to its index.
        seen = {}

        for index, value in enumerate(nums):
            complement = target - value

            # A match must come from an earlier, distinct element.
            if complement in seen:
                return [seen[complement], index]

            seen[value] = index

        # The standard problem guarantees a solution.
        raise ValueError("No two elements sum to the target")

Complexity:

  • Time: O(n), where n is the number of elements; each lookup and insertion is O(1) on average.
  • Space: O(n), for the dictionary of values seen so far.

Common follow-ups

  • What if there may be multiple valid pairs? Return the first pair found, or keep searching and collect pairs according to the required output rules.
  • What if the input is sorted? Use two pointers from opposite ends for O(1) extra space; preserve original indices if those are required.
  • What if values repeat? The dictionary stores indices, and checking before insertion ensures the current element is not paired with itself.
CodingEasyDeloitteSoftware Engineer

5. Valid Palindrome (LeetCode)

Model answer

The task

Determine whether a string reads the same forward and backward after disregarding characters that are not letters or digits and treating uppercase and lowercase as equivalent.

Key idea

Compare characters from both ends, skipping anything that should be ignored. If every remaining pair matches, the string is a palindrome.

Approach

  1. Initialize pointers at the start and end of the string.
  2. Move the left pointer past non-alphanumeric characters.
  3. Move the right pointer past non-alphanumeric characters.
  4. Compare the lowercase forms of the characters at the pointers; return False if they differ.
  5. Move both pointers inward and continue. If they meet or cross, return True.
class Solution:
    def isPalindrome(self, s: str) -> bool:
        left, right = 0, len(s) - 1

        while left < right:
            # Ignore characters that are not letters or digits.
            while left < right and not s[left].isalnum():
                left += 1
            while left < right and not s[right].isalnum():
                right -= 1

            # Compare without regard to letter case.
            if s[left].lower() != s[right].lower():
                return False

            left += 1
            right -= 1

        return True

Complexity:

  • Time: O(n), where n is the string length; each pointer moves only inward.
  • Space: O(1), since the comparisons use no additional data structure.

Common follow-ups

  • What if the input contains no letters or digits? It is considered a palindrome, since there are no mismatching characters.
  • Can you avoid creating a normalized copy of the string? Yes; the two-pointer scan uses constant extra space.
  • How would you handle Unicode normalization? Normalize the string first if canonically equivalent characters should compare as equal; that may require additional space.
CodingEasyDeloitteSoftware Engineer

6. Valid Parentheses (LeetCode)

Model answer

The task

Determine whether a string of opening and closing round, square, and curly brackets is correctly nested and matched. Return a boolean.

Key idea

A closing bracket must match the most recent unmatched opening bracket. A stack captures that order: push openings, and check each closing bracket against the stack’s top.

Approach

  1. Return False if the string has odd length.
  2. Map each closing bracket to its matching opening bracket.
  3. Scan the string from left to right.
  4. Push opening brackets onto the stack.
  5. For a closing bracket, return False if the stack is empty or its top does not match; otherwise pop.
  6. Return True only if the stack is empty after the scan.
class Solution:
    def isValid(self, s: str) -> bool:
        pairs = {")": "(", "]": "[", "}": "{"}
        openings = set(pairs.values())
        stack = []

        for char in s:
            if char in openings:
                stack.append(char)
            else:
                # A closing bracket must match the latest unmatched opening.
                if not stack or stack[-1] != pairs.get(char):
                    return False
                stack.pop()

        # Unmatched opening brackets make the sequence invalid.
        return not stack

Complexity:

  • Time: O(n), where n is the string length; each character is processed once.
  • Space: O(n) in the worst case, when all characters are opening brackets.

Common follow-ups

  • How would you support additional bracket types? Add their matching pairs to the map.
  • Can you stop early? Yes; return False as soon as a closing bracket mismatches or has no opening bracket.
  • What if the input includes non-bracket characters? Define how to handle them, then either ignore them or reject them during the scan.
CodingEasyDeloitteSoftware Engineer

7. Reverse Vowels of a String (LeetCode)

Model answer

The task

Given a string, return a new string with its vowels in reverse order. Keep every non-vowel character in its original position, and treat uppercase and lowercase vowels alike.

Key idea

Use two pointers, one at each end of the string. Move each pointer inward until it finds a vowel, then swap those vowels and continue.

Approach

  1. Convert the string to a list so its characters can be swapped.
  2. Put one pointer at the start and the other at the end.
  3. Advance the left pointer past non-vowels; retreat the right pointer past non-vowels.
  4. Swap the vowels when both pointers stop, then move both inward.
  5. Join the characters into the result.
class Solution:
    def reverseVowels(self, s: str) -> str:
        vowels = set("aeiouAEIOU")
        chars = list(s)
        left, right = 0, len(chars) - 1

        while left < right:
            while left < right and chars[left] not in vowels:
                left += 1
            while left < right and chars[right] not in vowels:
                right -= 1

            chars[left], chars[right] = chars[right], chars[left]
            left += 1
            right -= 1

        return "".join(chars)

Complexity:

  • Time: O(n), since the pointers traverse the string at most once.
  • Space: O(n), for the character list used to build the result.

Common follow-ups

  • How would you handle a different definition of vowels? Change the membership set to match it.
  • Can the method avoid creating a character list? Not for an immutable Python string; a mutable buffer would be needed.
  • What if there are no vowels or only one vowel? The pointers never swap distinct positions, so the string is returned unchanged.
CodingEasyDeloitteSoftware Engineer

8. Remove All Adjacent Duplicates In String (LeetCode)

Model answer

The task

Given a string, repeatedly remove any pair of equal characters that are next to each other. Return the string left after no such pair remains.

Key idea

Process the characters from left to right using a stack. The stack represents the reduced string so far: if the next character matches its top, remove the top; otherwise, add the character.

Approach

  1. Start with an empty stack.
  2. Visit each character in the input string.
  3. If it matches the stack’s top, pop the top.
  4. Otherwise, push the character.
  5. Join the stack to produce the result.

Each decision leaves the processed prefix fully reduced. Removing a pair can expose a new adjacent pair, but the stack handles that naturally when later characters are processed.

class Solution:
    def removeDuplicates(self, s: str) -> str:
        stack = []

        for char in s:
            if stack and stack[-1] == char:
                stack.pop()
            else:
                stack.append(char)

        return "".join(stack)

Complexity:

  • Time: O(n), since each character is pushed at most once and popped at most once.
  • Space: O(n), for the stack in the worst case.

Common follow-ups

  • What if each character must be removed when it appears in a run of at least k equal characters? Store character counts on the stack and pop a group when its count reaches k.
  • Can this be done in place on a mutable character array? Use a write index as a stack pointer, overwriting the processed prefix.
  • What if removals follow a specified priority or multiple different patterns? Clarify the rules first; a stack may still work, but the match and removal logic may need to change.
CodingEasyDeloitteSoftware Engineer

9. Add Binary (LeetCode)

Model answer

The task

Given two strings representing binary numbers, return their sum as a binary string.

Key idea

Add digits from right to left, keeping a carry just as in ordinary column addition. This avoids converting potentially long strings to integers.

Approach

  1. Start at the last character of each string with a carry of zero.
  2. While either string has digits left or a carry remains, add the available digits and the carry.
  3. Append the sum’s low bit; use the remaining value as the next carry.
  4. Reverse the collected digits to produce the result.
class Solution:
    def addBinary(self, a: str, b: str) -> str:
        i, j = len(a) - 1, len(b) - 1
        carry = 0
        result = []

        while i >= 0 or j >= 0 or carry:
            total = carry

            if i >= 0:
                total += int(a[i])
                i -= 1

            if j >= 0:
                total += int(b[j])
                j -= 1

            result.append(str(total % 2))
            carry = total // 2

        return "".join(reversed(result))

Complexity:

  • Time: O(max(n, m)), where n and m are the input lengths.
  • Space: O(max(n, m)) for the output digits.

Common follow-ups

  • How would you handle signed binary values? Define a representation such as two’s complement, then account for its fixed width.
  • Can you avoid converting each character to an integer? Subtract '0' from each character to obtain its digit.
  • What if the inputs contain leading zeroes? The addition still works; optionally normalize the returned string.
  • How would you add many binary strings? Repeatedly apply pairwise addition, or use a suitable multi-operand addition strategy.
CodingEasyDeloitteSoftware Engineer

10. Remove Duplicates from Sorted Array (LeetCode)

Model answer

The task

Given a sorted list of integers, modify it in place so each distinct value appears once in the front. Return the length of that unique prefix.

Key idea

Because the values are sorted, duplicates are adjacent. Keep a read pointer to scan the list and a write pointer for the next position where a new value belongs.

Approach

  1. If the list is empty, return 0.
  2. Start the write pointer at 1, since the first value is already unique.
  3. Scan from left to right.
  4. When the current value differs from the last value written, copy it to the write position and advance the pointer.
  5. Return the write pointer as the unique-prefix length.
from typing import List

class Solution:
    def removeDuplicates(self, nums: List[int]) -> int:
        if not nums:
            return 0

        write = 1

        # Copy each newly encountered value into the unique prefix.
        for read in range(1, len(nums)):
            if nums[read] != nums[write - 1]:
                nums[write] = nums[read]
                write += 1

        return write

Complexity:

  • Time: O(n), where n is the length of the list.
  • Space: O(1), since the list is modified in place using only a fixed number of variables.

Common follow-ups

  • What if the list is not sorted? Use a set to track seen values while compacting unique values in their original order.
  • What if each value may appear at most twice? Write a value when fewer than two values have been written, or when it differs from the value two positions back.
  • What if the list is very large and cannot fit in memory? Process sorted chunks while carrying the last value across chunk boundaries.
CodingEasyDeloitteSoftware Engineer

11. Longest Common Prefix (LeetCode)

Model answer

The task

Given a list of strings, return the longest string that is a prefix of every entry. If there is no shared prefix, return an empty string.

Key idea

Compare characters at the same position across all strings, starting from the first string. Stop at the first mismatch or when any string runs out.

Approach

  1. If the list is empty, return an empty string.
  2. Scan each character of the first string from left to right.
  3. For each position, compare that character with the character at the same position in every other string.
  4. If a string is too short or a character differs, return the matching prefix before that position.
  5. If all positions match, return the first string.
from typing import List

class Solution:
    def longestCommonPrefix(self, strs: List[str]) -> str:
        if not strs:
            return ""

        first = strs[0]

        for i, char in enumerate(first):
            for word in strs[1:]:
                if i == len(word) or word[i] != char:
                    return first[:i]

        return first

Complexity:

  • Time: O(S), where S is the total number of characters inspected.
  • Space: O(1) auxiliary space, excluding the returned prefix.

Common follow-ups

  • How would you handle many prefix queries over the same set of strings? Build a trie once, then traverse it to find shared prefixes.
  • Could sorting help? Yes. Sort the strings and compare only the first and last; their common prefix is shared by all strings.
  • What if strings arrive as a stream? Keep a running prefix and shorten it as each new string arrives.
CodingEasyDeloitteSoftware Engineer

12. Best Time to Buy and Sell Stock (LeetCode)

Model answer

The task

Given daily stock prices, find the greatest profit possible from one buy followed by one later sale. If no sale would make money, return zero.

Key idea

As we scan the prices, keep the lowest price seen so far. Treat each current price as a possible sale and update the best profit using the difference from that minimum.

Approach

  1. Initialize the minimum price to a value larger than any price and the best profit to zero.
  2. Visit each price in order.
  3. Update the minimum price with the current price.
  4. Check the profit from selling today and update the best profit.
  5. Return the best profit.
class Solution:
    def maxProfit(self, prices: list[int]) -> int:
        min_price = float("inf")
        best_profit = 0

        for price in prices:
            # The current price can be a new best day to buy.
            min_price = min(min_price, price)

            # Or it can be the day to sell after the cheapest earlier buy.
            best_profit = max(best_profit, price - min_price)

        return best_profit

Complexity:

  • Time: O(n), where n is the number of prices; each is processed once.
  • Space: O(1), since only a few variables are stored.

Common follow-ups

  • What if multiple buy-and-sell transactions are allowed? Sum the gains from every adjacent price increase.
  • What if a transaction fee is charged? Track the best holding and non-holding profits, incorporating the fee when selling.
  • What if at most k transactions are allowed? Use dynamic programming over the number of completed transactions and whether a share is currently held.
CodingEasyDeloitteSoftware Engineer

13. Climbing Stairs (LeetCode)

Model answer

The task

Count how many distinct ways there are to reach the top of a staircase with n steps when each move advances either one or two steps.

Key idea

To reach step i, the last move must come from step i - 1 or i - 2. Thus the number of ways to reach i is the sum of the ways to reach those two earlier steps. Only the previous two counts are needed, so there is no need to store a full table.

Approach

  1. Set the number of ways to reach step 0 to 1, representing the single empty sequence of moves.
  2. Set the number of ways to reach step 1 to 1.
  3. For each step from 2 through n, add the counts for the preceding two steps.
  4. Return the count for step n.
class Solution:
    def climbStairs(self, n: int) -> int:
        # Ways to reach steps 0 and 1.
        two_back = 1
        one_back = 1

        # Build the count using the two preceding results.
        for _ in range(2, n + 1):
            current = two_back + one_back
            two_back = one_back
            one_back = current

        return one_back

Complexity:

  • Time: O(n), since each step is processed once.
  • Space: O(1), since only two prior counts are retained.

Common follow-ups

  • What if each move can advance 1, 2, or 3 steps? Sum the counts for the previous three steps.
  • What if the result must be returned modulo a number? Apply the modulus after each addition.
  • What if some steps are forbidden? Set their ways count to zero and use dynamic programming for the allowed steps.
CodingEasyDeloitteSoftware Engineer

14. First Unique Character in a String (LeetCode)

Model answer

The task

Return the index of the first character in a string that occurs exactly once. If no character is unique, return -1.

Key idea

Count how often each character appears, then scan the string from left to right. The first character with a count of one has the smallest qualifying index.

Approach

  1. Build a frequency map by counting every character.
  2. Scan the string in its original order.
  3. Return the index of the first character whose count is one.
  4. If the scan finishes without finding one, return -1.
class Solution:
    def firstUniqChar(self, s: str) -> int:
        # Count the occurrences of each character.
        counts = {}
        for char in s:
            counts[char] = counts.get(char, 0) + 1

        # Return the earliest character that appears once.
        for index, char in enumerate(s):
            if counts[char] == 1:
                return index

        return -1

Complexity:

  • Time: O(n), for counting and scanning the string.
  • Space: O(k), where k is the number of distinct characters stored in the map.

Common follow-ups

  • What if the string is streamed and cannot be revisited? Keep character counts and a queue of candidates; discard candidates once their count exceeds one.
  • What if characters come from a fixed, small alphabet? Use an array of counters instead of a hash map.
  • How would you return the unique character rather than its index? Return char at the matching position, with an appropriate sentinel if none exists.
CodingEasyDeloitteSoftware Engineer

15. Rising Temperature (LeetCode)

Model answer

The task

Find each weather record whose temperature is higher than the temperature recorded on the immediately preceding calendar day. Return the IDs of those records.

Key idea

Join the table to itself, matching each record with the record dated exactly one day earlier. Comparing only adjacent rows would be incorrect because dates may have gaps.

Approach

  1. Treat one copy of the table as the current day and another as the previous day.
  2. Join them where the current date is one day after the previous date.
  3. Keep pairs where the current temperature is higher.
  4. Return the current record’s ID.
SELECT current_day.id AS Id
FROM Weather AS current_day
JOIN Weather AS previous_day
  ON current_day.recordDate = DATE_ADD(previous_day.recordDate, INTERVAL 1 DAY)
WHERE current_day.temperature > previous_day.temperature;

Complexity:

  • Time: O(n log n) with an index on recordDate; the exact cost depends on the database’s query plan.
  • Space: O(1) auxiliary space, excluding space used for the result and any database join workspace.

Common follow-ups

  • What if there are missing dates? The exact one-day join naturally skips dates without a prior-day record.
  • How could this perform better on a large table? Add an index on recordDate and inspect the database’s execution plan.
  • How would you adapt this for another SQL dialect? Replace DATE_ADD with that dialect’s date arithmetic, such as a one-day interval expression.
CodingEasyDeloitteSoftware Engineer

16. Valid Anagram (LeetCode)

Model answer

The task

Determine whether two strings contain the same characters with the same frequencies, regardless of order.

Key idea

Anagrams have identical character counts. Count the characters in each string and compare the resulting frequency maps.

Approach

  1. If the strings have different lengths, return False.
  2. Build a frequency map for each string.
  3. Return whether the maps are equal.
from collections import Counter

class Solution:
    def isAnagram(self, s: str, t: str) -> bool:
        # Different lengths cannot have matching character counts.
        if len(s) != len(t):
            return False

        # Anagrams have exactly the same character frequencies.
        return Counter(s) == Counter(t)

Complexity:

  • Time: O(n), where n is the length of either string; counting and comparison are linear.
  • Space: O(k), where k is the number of distinct characters stored in the frequency maps.

Common follow-ups

  • Can you do this without two maps? Update one frequency map using s, then decrement counts using t; verify all counts return to zero.
  • What if the input is guaranteed to contain only lowercase English letters? Use a fixed-size array of 26 counters instead of maps.
  • What if sorting is allowed? Sort both strings and compare them; this takes O(n log n) time.
CodingEasyDeloitteSoftware Engineer

17. Customers Who Never Order (LeetCode)

Model answer

The task

Find the names of customers who have no matching order.

Key idea

Use a LEFT JOIN to keep every customer, then select rows where the joined order is missing.

Approach

  1. Start with every row in Customers.
  2. Match orders using the customer ID.
  3. Keep only rows with no matching order.
  4. Return the customer name under the required column name.
SELECT c.Name AS Customers
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.Id
WHERE o.Id IS NULL;

Complexity:

  • Time: O(C + O) with suitable indexes, where C and O are the numbers of customers and orders.
  • Space: O(1) extra space, excluding database execution and result storage.

Common follow-ups

  • How could you write this without a join? Use NOT EXISTS to check for the absence of a matching order.
  • What if customer names are not unique? Return one row per customer; do not use DISTINCT unless unique names are specifically requested.
  • What indexes help? An index on Orders.CustomerId can speed up the match.
CodingMediumDeloitteData ScientistSQL screen

18. How Would You Calculate Customer Retention Using SQL?

Model answer

The flow

  1. Read the schema: Understand tables related to customer transactions and accounts.
  2. Pick the grain: Decide on the time frame for retention calculation (e.g., monthly, quarterly).
  3. Join/filter: Join customer and transaction tables, filter relevant time frames.
  4. Aggregate or window: Use window functions to identify returning customers.
  5. Check edge cases: Handle NULLs, duplicates, and define what constitutes a "returning" customer.

The answer

1. Read the schema

  • Identify tables like customers, transactions, and any other relevant tables.
  • Assume customers has customer_id and signup_date.
  • Assume transactions has customer_id, transaction_date, and amount.

2. Pick the grain

  • Decide to calculate retention on a monthly basis.
  • Define retention as customers who made a purchase in a given month and returned in the next month.

3. Join/filter

  • Join customers and transactions on customer_id.
  • Filter transactions to focus on the relevant time frame.
SELECT c.customer_id, t.transaction_date
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

4. Aggregate or window

  • Use a window function to check if a customer made a transaction in the following month.
WITH monthly_transactions AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', transaction_date) AS month
  FROM transactions
  GROUP BY customer_id, month
)
SELECT 
  mt.customer_id,
  mt.month,
  LEAD(mt.month) OVER (PARTITION BY mt.customer_id ORDER BY mt.month) AS next_month
FROM monthly_transactions mt;

5. Check edge cases

  • Handle NULLs in next_month to identify customers who did not return.
  • Consider customers who joined late in the year.
SELECT 
  customer_id,
  month,
  CASE WHEN next_month IS NOT NULL THEN 1 ELSE 0 END AS retained
FROM (
  SELECT 
    mt.customer_id,
    mt.month,
    LEAD(mt.month) OVER (PARTITION BY mt.customer_id ORDER BY mt.month) AS next_month
  FROM monthly_transactions mt
) retention_status;

Why this works

  • Schema understanding: Tests the candidate's ability to navigate and interpret database schemas.
  • Time frame selection: Evaluates decision-making in defining retention periods.
  • SQL proficiency: Assesses the ability to use joins, filters, and window functions effectively.
  • Edge case handling: Checks the candidate's attention to detail in handling NULLs and defining retention criteria.
  • Common pitfalls: Weak answers might miss edge cases, use inefficient queries, or fail to define retention clearly.
Product & growthMediumDeloitteData ScientistProduct / analytics round

19. A Recommendation Engine Is Producing Poor Suggestions.

The full question

A Recommendation Engine Is Producing Poor Suggestions. How Would You Diagnose the Issue?

Model answer

The flow

  1. Clarify the goal → Understand what success looks like for the recommendation engine.
  2. Define the metric → Identify key metrics that measure recommendation quality.
  3. Break it down by funnel and segment → Analyze the recommendation process and user segments.
  4. Rank hypotheses → List potential causes for poor suggestions and prioritize them.
  5. Investigate each hypothesis → Plan how to test each hypothesis.
  6. Decision & guardrails → Decide on actions based on findings and set future monitoring.

The answer

Clarify the goal

  • The goal is to improve the relevance and accuracy of the recommendations provided to users.
  • Success means higher user engagement, increased click-through rates (CTR), and improved user satisfaction.

Define the metric

  • Key metrics include CTR, conversion rate, and user retention rate.
  • Additional metrics: diversity of recommendations, novelty, and user feedback scores.

Break it down by funnel and segment

  • Funnel: Data collection → Feature extraction → Model training → Recommendation generation → User feedback.
  • Segments: New users vs. returning users, different user demographics, and behavior patterns.
funnel
    subgraph Funnel
    A[Data Collection] --> B[Feature Extraction]
    B --> C[Model Training]
    C --> D[Recommendation Generation]
    D --> E[User Feedback]
    end
Diagram

Rank hypotheses

  • Hypothesis 1: Data quality issues (e.g., missing or outdated data).
  • Hypothesis 2: Model overfitting or underfitting.
  • Hypothesis 3: Inadequate feature selection or extraction.
  • Hypothesis 4: Changes in user behavior not captured by the model.
  • Hypothesis 5: Poor diversity or novelty in recommendations.

Investigate each hypothesis

  • For data quality, check logs for missing or outdated data entries.
  • For model issues, evaluate model performance on validation sets and check for overfitting.
  • For feature selection, review feature importance scores and consider new features.
  • For user behavior changes, analyze recent user interaction data.
  • For diversity, calculate the diversity score of recommendations and compare it to benchmarks.

Decision & guardrails

  • If data quality is the issue, implement data cleaning processes.
  • If the model is overfitting, consider regularization techniques or more data.
  • If features are lacking, conduct feature engineering to improve input data.
  • If user behavior has changed, update the model more frequently.
  • Monitor key metrics regularly to ensure sustained improvement.

Why this works

  • Comprehensive diagnosis: The approach systematically covers all potential causes, ensuring no stone is left unturned.
  • Prioritization: By ranking hypotheses, resources are efficiently allocated to the most likely issues.
  • Metric-driven: Using clear metrics provides a quantifiable way to measure improvement.
  • Iterative improvement: The process allows for continuous monitoring and adjustment, ensuring long-term success.
  • Weak answers: Would fail to identify specific issues, lack prioritization, or not provide actionable steps for improvement.
Product & growthMediumDeloitteManagement ConsultantCase interview

20. How many mobile phones are lost each year at music festivals in the UK?

Model answer

The flow

  1. Segment the population: Identify the number of people attending music festivals in the UK annually.
  2. Apply penetration: Estimate the percentage of attendees who own mobile phones.
  3. Frequency of loss: Determine the likelihood of losing a phone at a festival.
  4. Calculate total losses: Multiply the number of attendees by the penetration and frequency rates.
  5. Sanity-check: Validate the final number by comparing it to known statistics or logical reasoning.

The answer

Segment the population

  • Assume there are about 100 major music festivals in the UK annually, with an average attendance of 50,000 people per festival.
  • Total attendees = 100 festivals * 50,000 attendees = 5,000,000 attendees.

Apply penetration

  • Assume 95% of festival-goers own a mobile phone.
  • Mobile phone owners = 5,000,000 * 0.95 = 4,750,000 mobile phone owners.

Frequency of loss

  • Estimate that 1% of attendees lose their phones during a festival.
  • Phones lost = 4,750,000 * 0.01 = 47,500 phones.

Calculate total losses

  • Total mobile phones lost annually at music festivals in the UK = 47,500.

Sanity-check

  • This number seems reasonable given the large number of attendees and the chaotic environment of festivals. Cross-referencing with anecdotal reports or insurance claims could provide further validation.

Why this works

  • Testing estimation skills: The interviewer is assessing your ability to break down a problem into manageable parts and make reasonable assumptions.
  • Logical structure: A strong answer follows a clear, logical structure, ensuring that each step builds on the previous one.
  • Sanity-check: A strong candidate will validate their final number, demonstrating awareness of the need for reasonable outputs.
  • Weak answers: Often fail by making unrealistic assumptions, skipping steps, or not validating the final estimate against known data or logical reasoning.

Practice these out loud, don't memorise them

Reading an answer is not the same as being able to give one under pressure. ChannelPulse plays the interviewer, asks the follow-ups, and scores each answer with feedback and a model answer so you can hear the gap between what you said and what lands.

Get ChannelPulse Browse all questions