100 Essential SQL & Python Patterns for Meta Data Engineer Screening (with Practice Problems)

The Meta Data Engineer screening is a fast-paced, 60-minute challenge where you’ll solve 3–5 SQL and 3–5 Python problems. To ace it, you must master a wide range of data manipulation, aggregation, and algorithmic patterns. Below is an exhaustive catalog of 50 SQL and 50 Python problem patterns—each with similar practice problems from LeetCode, StrataScratch, or DataLemur—so you can target your prep and recognize every major scenario.

50 Essential SQL Patterns: Concepts & Key Ideas

• Conditional Aggregation & Percentages
• Concept: Aggregate counts based on a condition, then calculate a percentage for a subcondition.
• Key Idea: Use CASE WHEN inside SUM/COUNT for flexible metrics.
• LeetCode 584, StrataScratch
• Lifetime Value/Total Usage
• Concept: Sum durations or values for each entity, often filtering out incomplete records.
• Key Idea: Use date arithmetic and GROUP BY.
• LeetCode 1070, DataLemur
• Difference Between Related Entities
• Concept: Compare metrics between an entity and a related entity (e.g., referrer, manager).
• Key Idea: Use self-joins and ABS().
• StrataScratch
• Consecutive Time Periods & Unique Counts
• Concept: Identify users with actions in consecutive periods, then rank by unique actions.
• Key Idea: Use EXTRACT, window functions, and COUNT(DISTINCT ...).
• LeetCode 550
• Revenue & Cost Aggregation
• Concept: Aggregate revenue and cost from different tables or columns for comparison.
• Key Idea: Use SUM across multiple tables, often with UNION or subqueries.
• LeetCode 1075
• Entities With No Related Activity
• Concept: Find records in one table with no matching records in another.
• Key Idea: Use LEFT JOIN ... IS NULL or NOT EXISTS.
• LeetCode 183
• Top N by Unique Interactions
• Concept: Rank entities by the number of unique related items.
• Key Idea: GROUP BY + COUNT(DISTINCT ...), then ORDER BY and LIMIT.
• LeetCode 185
• Multi-Group/Category Logic
• Concept: Find entities associated with multiple groups or categories.
• Key Idea: GROUP BY + HAVING COUNT(DISTINCT ...) > 1.
• StrataScratch
• Window Functions & Ranking
• Concept: Assign ranks or compute running totals within groups.
• Key Idea: Use ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER().
• LeetCode 176
• Rolling or Cumulative Sums
• Concept: Track cumulative metrics over time.
• Key Idea: SUM() OVER (ORDER BY ...) for running totals.
• LeetCode 601
• Median/Aggregate with Window Functions
• Concept: Calculate medians or other aggregates using window functions.
• Key Idea: Use NTILE, PERCENT_RANK, or custom logic.
• LeetCode 569
• Find Duplicates
• Concept: Identify records with duplicate values in a field.
• Key Idea: GROUP BY + HAVING COUNT(*) > 1.
• LeetCode 196
• Most Recent/Oldest Record
• Concept: Find the latest or earliest record per group.
• Key Idea: MAX/MIN with GROUP BY or window functions.
• LeetCode 184
• Join Multiple Tables for a Metric
• Concept: Combine data from several tables to compute a metric.
• Key Idea: Multiple JOINs, careful aliasing.
• LeetCode 570
• Self-Join for Hierarchies
• Concept: Compare entities within the same table (e.g., employees and managers).
• Key Idea: JOIN table to itself on a parent-child relationship.
• LeetCode 177
• Anti-Join for Missing Relationships
• Concept: Find entities missing a relationship (e.g., no friends, no sales).
• Key Idea: LEFT JOIN ... IS NULL or NOT EXISTS.
• LeetCode 602
• Date Extraction and Grouping
• Concept: Group data by extracted date parts (year, month, week).
• Key Idea: Use EXTRACT or DATE_TRUNC.
• LeetCode 1158
• Aggregation Over Filtered Subgroups
• Concept: Compute aggregates for subgroups meeting a condition.
• Key Idea: WHERE + GROUP BY + HAVING.
• LeetCode 586
• Percentile/Quantile Calculations
• Concept: Find the median or other quantiles.
• Key Idea: Window functions, NTILE, or custom logic.
• DataLemur
• Cohort Analysis
• Concept: Track user groups based on signup or first activity over time.
• Key Idea: Assign cohort labels using CASE or window functions.
• StrataScratch
• Churn/Retention Rate
• Concept: Measure how many users return after a time period.
• Key Idea: Use joins and date logic to compare periods.
• DataLemur
• Funnel Drop-off
• Concept: Track user drop-off at each funnel stage.
• Key Idea: Use event sequences and group by user/stage.
• DataLemur
• Sessionization
• Concept: Group user actions into sessions based on time gaps.
• Key Idea: Use window functions and time difference.
• StrataScratch
• First/Last Occurrence per Group
• Concept: Find the first or last event per user/group.
• Key Idea: Use ROW_NUMBER() + ORDER BY or aggregate min/max.
• LeetCode 586
• Running Totals
• Concept: Calculate cumulative sums or counts.
• Key Idea: SUM() OVER (ORDER BY ...).
• LeetCode 601
• Find All Entities with a Minimum/Maximum Value
• Concept: Find all entities tied for min/max in a group.
• Key Idea: Use RANK() or join to subquery with max/min.
• LeetCode 184
• Entities with Only One Related Record
• Concept: Filter groups with exactly one related item.
• Key Idea: GROUP BY + HAVING COUNT(*) = 1.
• LeetCode 595
• Multi-Level Grouping
• Concept: Aggregate by multiple fields (e.g., department and title).
• Key Idea: GROUP BY multiple columns.
• StrataScratch
• Pivot/Unpivot Data
• Concept: Convert rows to columns or vice versa.
• Key Idea: Use CASE WHEN or aggregate functions.
• DataLemur
• Event Sequence Analysis
• Concept: Analyze ordered sequences of events.
• Key Idea: Use window functions and lag/lead.
• StrataScratch
• Calculate Ratios
• Concept: Compute ratios between two aggregates.
• Key Idea: Use subqueries or CTEs for numerator/denominator.
• LeetCode 607
• Find All Pairs Satisfying a Condition
• Concept: List all pairs of entities meeting a relationship.
• Key Idea: Use self-join or cross join with a filter.
• LeetCode 175
• Time Series Gaps
• Concept: Find missing dates or gaps in sequences.
• Key Idea: Use window functions and date arithmetic.
• StrataScratch
• Find Top N per Group
• Concept: Find the top N items within each group.
• Key Idea: Use ROW_NUMBER() or RANK() partitioned by group.
• LeetCode 185
• Rank Entities by Multiple Criteria
• Concept: Rank by one field, break ties with another.
• Key Idea: ORDER BY multiple columns in window function.
• LeetCode 178
• Calculate Growth Rates
• Concept: Find growth between periods.
• Key Idea: Use window functions to compare current and previous.
• DataLemur
• Find All Entities Not in Another Table
• Concept: List all entities missing from a related table.
• Key Idea: LEFT JOIN ... IS NULL.
• LeetCode 183
• Find Overlapping Intervals
• Concept: Identify overlapping time intervals.
• Key Idea: Join on interval conditions.
• StrataScratch
• Entities with Multiple Types of Related Records
• Concept: Find entities linked to multiple types or categories.
• Key Idea: GROUP BY + HAVING COUNT(DISTINCT type) > 1.
• StrataScratch
• Entities with the Most/Fewest Related Records
• Concept: Find entities with max/min count of related items.
• Key Idea: Rank by count and filter.
• LeetCode 586
• Calculate Cumulative Unique Counts
• Concept: Running count of unique users/items.
• Key Idea: Use window functions and COUNT(DISTINCT ...).
• StrataScratch
• Find Sequences of Events
• Concept: Detect ordered patterns in event logs.
• Key Idea: Use LAG/LEAD and window partitioning.
• StrataScratch
• Entities with All/None of a Set of Related Records
• Concept: Entities with all or none of a set of relationships.
• Key Idea: Use GROUP BY + HAVING COUNT(DISTINCT ...) = X.
• LeetCode 602
• Entities with at Least N Related Records
• Concept: Filter for entities with a minimum number of relationships.
• Key Idea: GROUP BY + HAVING COUNT(*) >= N.
• LeetCode 570
• Entities with No Related Records in a Time Window
• Concept: Find users inactive in a given period.
• Key Idea: Use date filters and anti-join.
• StrataScratch
• Average per Group
• Concept: Compute average value per group.
• Key Idea: GROUP BY + AVG().
• LeetCode 577
• Entities with a Value Above/Below a Threshold
• Concept: Filter by value criteria.
• Key Idea: WHERE value > X.
• LeetCode 595
• Entities with a Value in a Range
• Concept: Filter by value range.
• Key Idea: WHERE value BETWEEN X AND Y.
• LeetCode 175
• Entities with a Value Matching a Pattern
• Concept: Use pattern matching in filtering.
• Key Idea: WHERE field LIKE 'pattern%'.
• LeetCode 595
• Entities with Multiple Related Records Meeting a Condition
• Concept: Filter by multiple relationships with a condition.
• Key Idea: GROUP BY + HAVING SUM(CASE WHEN ...) > X.
• StrataScratch

• 50 Essential Python Patterns: Concepts & Key Ideas
• Category Selection for Maximum Value
• Concept: Pick up to N items from different categories for max sum.
• Key Idea: Use dictionary for best per category, sort, sum top N.
• LeetCode 347
• Subsequence/Transformation Validation
• Concept: Check if a string can be formed by deleting characters from another.
• Key Idea: Two pointers; scan both strings.
• LeetCode 392
• Resource Redistribution
• Concept: Redistribute resources among entities based on closures or restrictions.
• Key Idea: Simulate transfers using nested dictionaries or arrays.
• LeetCode 1094
• Interval Merging
• Concept: Merge overlapping intervals.
• Key Idea: Sort intervals, merge if overlapping.
• LeetCode 56
• Maximum Overlap/Simultaneous Events
• Concept: Find the max number of overlapping intervals/events.
• Key Idea: Use a sweep line or sort start/end points.
• LeetCode 253
• Greedy Assignment
• Concept: Assign tasks/resources to maximize allocation under constraints.
• Key Idea: Sort both lists, assign greedily.
• LeetCode 455
• Graph Traversal (Nth Degree Connections)
• Concept: Find all nodes N steps away in a graph/tree.
• Key Idea: BFS or DFS with depth tracking.
• LeetCode 429
• String Manipulation
• Concept: Find the length of the longest substring without repeating characters.
• Key Idea: Sliding window with hash set/dictionary.
• LeetCode 3
• Hashmap Counting
• Concept: Find the element that appears most frequently.
• Key Idea: Use collections.Counter or a dictionary.
• LeetCode 169
• Two-Pointer Technique
• Concept: Find two numbers in a sorted array that sum to a target.
• Key Idea: One pointer at each end, move inward.
• LeetCode 167
• Sliding Window Maximum/Minimum
• Concept: Find max/min in a moving window.
• Key Idea: Use deque for O(n) solution.
• LeetCode 239
• Binary Search
• Concept: Search for a value in a sorted array.
• Key Idea: Divide and conquer, O(log n).
• LeetCode 704
• DFS/BFS on Graphs
• Concept: Traverse all nodes in a graph.
• Key Idea: Use stack (DFS) or queue (BFS).
• LeetCode 200
• Dynamic Programming: Subset Sum
• Concept: Can a subset sum to a target value?
• Key Idea: DP table for possible sums.
• LeetCode 416
• Dynamic Programming: Longest Increasing Subsequence
• Concept: Find the longest increasing subsequence in an array.
• Key Idea: DP with memoization.
• LeetCode 300
• Heap/Priority Queue
• Concept: Retrieve the largest/smallest elements efficiently.
• Key Idea: Use heapq for O(log n) insert/extract.
• LeetCode 215
• Matrix Traversal
• Concept: Visit every cell in a matrix in a specific order.
• Key Idea: Nested loops or direction vectors.
• LeetCode 54
• Backtracking: Permutations
• Concept: Generate all possible orderings.
• Key Idea: Recursion with used/unused tracking.
• LeetCode 46
• Backtracking: Combinations
• Concept: Generate all subsets of a certain size.
• Key Idea: Recursion with start index.
• LeetCode 77
• Bit Manipulation
• Concept: Use bitwise operations for set/flag problems.
• Key Idea: XOR, AND, OR, bit shifts.
• LeetCode 136
• Palindrome Check
• Concept: Check if a string is a palindrome.
• Key Idea: Compare string to its reverse or use two pointers.
• LeetCode 5
• Linked List Manipulation
• Concept: Reverse or modify linked lists.
• Key Idea: Pointer manipulation.
• LeetCode 206
• Tree Traversal
• Concept: Visit all nodes in a tree (inorder, preorder, postorder).
• Key Idea: Recursion or stack.
• LeetCode 94
• Trie/Prefix Tree
• Concept: Efficient prefix search.
• Key Idea: Nested dictionaries or custom node classes.
• LeetCode 208
• LRU Cache Implementation
• Concept: Design a cache with least recently used eviction.
• Key Idea: Use OrderedDict or custom doubly linked list.
• LeetCode 146
• Find All Anagrams
• Concept: Find all anagrams of a word in a string.
• Key Idea: Sliding window with character counts.
• LeetCode 438
• Group Anagrams
• Concept: Group words that are anagrams.
• Key Idea: Use sorted tuple of letters as dict key.
• LeetCode 49
• Rotate Array
• Concept: Shift elements in an array.
• Key Idea: Slicing or reverse in place.
• LeetCode 189
• Jump Game
• Concept: Can you reach the end of an array with jumps?
• Key Idea: Greedy check for reachable indices.
• LeetCode 55
• Minimum Window Substring
• Concept: Find the smallest substring containing all characters of a target.
• Key Idea: Sliding window with hashmaps.
• LeetCode 76
• Find Cycle in Linked List
• Concept: Detect loops in linked lists.
• Key Idea: Fast/slow pointers.
• LeetCode 141
• Find Intersection of Two Arrays
• Concept: Return common elements between two arrays.
• Key Idea: Use sets for O(1) lookup.
• LeetCode 349
• Find Missing Number
• Concept: Find a missing integer in a sequence.
• Key Idea: Sum formula or XOR.
• LeetCode 268
• Find Duplicate Number
• Concept: Find a repeated number in a list.
• Key Idea: Cycle detection or set/hashmap.
• LeetCode 287
• Merge Sorted Arrays
• Concept: Combine two sorted arrays.
• Key Idea: Two pointers or built-in sort.
• LeetCode 88
• Find Peak Element
• Concept: Find a local maximum in an array.
• Key Idea: Binary search for O(log n).
• LeetCode 162
• Find First Unique Character
• Concept: Find the first non-repeating character.
• Key Idea: Hashmap for counts, then scan.
• LeetCode 387
• Valid Parentheses
• Concept: Check if parentheses are balanced.
• Key Idea: Stack to match open/close.
• LeetCode 20
• Implement Stack/Queue
• Concept: Build stack or queue from scratch.
• Key Idea: Use lists or collections.deque.
• LeetCode 225
• Find Shortest Path in Grid
• Concept: BFS for shortest path in a matrix.
• Key Idea: Queue and visited set.
• LeetCode 1091
• Find All Paths in Graph
• Concept: Return all possible paths from source to target.
• Key Idea: DFS with path tracking.
• LeetCode 797
• Word Ladder
• Concept: Transform one word to another by changing one letter at a time.
• Key Idea: BFS with word neighbors.
• LeetCode 127
• Subarray Sum Equals K
• Concept: Number of subarrays summing to k.
• Key Idea: Prefix sums with hashmap.
• LeetCode 560
• Find Longest Consecutive Sequence
• Concept: Find the length of the longest sequence of consecutive numbers.
• Key Idea: Set for O(1) lookup, expand from each number.
• LeetCode 128
• Find All Subsets
• Concept: Generate all possible subsets of a set.
• Key Idea: Backtracking or bitmasking.
• LeetCode 78
• Find All Permutations
• Concept: Generate all orderings.
• Key Idea: Backtracking with used/unused.
• LeetCode 46
• Find All Combinations
• Concept: Generate all combinations of a certain size.
• Key Idea: Recursive backtracking.
• LeetCode 77
• Find Kth Smallest/Largest
• Concept: Find the kth smallest/largest element.
• Key Idea: Heap or quickselect.
• LeetCode 215
• Find Minimum in Rotated Sorted Array
• Concept: Find min in rotated sorted array.
• Key Idea: Modified binary search.
• LeetCode 153
• Find Elements Appearing More Than N/3 Times
• Concept: Find elements with high frequency.
• Key Idea: Boyer-Moore or hashmap.
• LeetCode 229

How to Use This Guide

• Recognize the pattern: When you see a new problem, map it to one of these patterns.
• Understand the key idea: Each pattern has a core technique (window function, two pointers, hashmaps, etc.).
• Practice with the linked problems: These are the best way to master the pattern.
• Simulate Meta’s format: Try 3–5 SQL and 3–5 Python problems in 60 minutes.

By mastering these patterns and their core concepts, you’ll be ready for any Meta Data Engineer screening—no matter how the question is phrased or what the dataset looks like.

Happy practicing and good luck!