SQL and Python adtech interview questions: a complete guide

Informatec Digital » Resources » SQL and Python adtech interview questions: a complete guide

If you're preparing for an interview in adtech , digital analytics, or data analysis , sooner or later you'll have to face the dreaded technical test. That's where companies check if you truly master what you put on your CV: SQL to extract data, Python to process and analyze it, and some analytical thinking skills to avoid getting lost among all the tables and scripts.

The good news is that technical interviews aren't magic: they usually revolve around the same blocks of SQL, Python, and model evaluation . If you thoroughly understand the fundamentals, practice with real-world exercises, and learn to explain your reasoning, you'll have a significant advantage over other candidates.

Why technical interviews are scary (and how to take the edge off them)

In data analyst or product roles in adtech, technical interviews typically combine conceptual questions with live, hands-on exercises . You might be asked to explain the function of an SQL clause or solve a business case involving programmatic advertising campaign data.

You'll typically be assessed on three fronts: intermediate-level SQL (JOIN, GROUP BY, subqueries, window functions), data-oriented Python (pandas, cleaning, aggregations, merges), and your ability to interpret results and communicate findings . They're not looking for a senior data scientist, but rather someone who can work with data in a robust and reliable way.

The typical mistake many candidates make is focusing on memorizing syntax and forgetting to practice complete exercises , similar to those you'll encounter on platforms like HackerRank, StrataScratch, or the companies' own internal tests. Your goal should be to arrive at the interview having already solved dozens of very similar queries and scripts.

SQL level typically required in adtech and data analyst interviews

For an analyst role in adtech or digital marketing environments, companies expect you to be proficient in classic relational SQL : extracting data from multiple tables, combining, grouping, filtering, and creating useful metrics. They won't require database administration skills, but you should be comfortable with moderately complex queries.

Typically, the test will include queries that combine INNER JOIN, LEFT JOIN, filters with WHERE clauses, aggregations with GROUP BY , and some conditions on aggregates using HAVING clauses. From there, in slightly more advanced positions, it's very common to see subqueries and window functions for rankings, cumulative totals, and row comparisons.

In adtech, you'll likely be answering business questions like "Which campaigns have a better CTR than the average for their industry?" or "Which publishers are losing impressions compared to last month?" Solving these efficiently usually requires a basic understanding of correlated subqueries and window functions.

Basic SQL questions that commonly appear (and how to answer them)

Almost every data-oriented technical interview includes a set of basic SQL theory questions . They're not trying to catch you out, but rather to ensure you have a solid foundation.

One of the most frequently asked questions is the difference between WHERE and HAVING . The clearest way to explain it is that WHERE filters individual rows before grouping , while HAVING filters groups that have already been aggregated . You can also mention that conditions involving aggregation functions (COUNT, SUM, AVG, etc.) should be placed in HAVING, not WHERE.

Another classic question is what types of JOIN exist and when to use each one: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and, in some cases, CROSS JOIN or self-join. In data analysis, LEFT JOIN is used extensively when you want to retain the entire primary dataset (for example, all users) even if there are no associated records in the secondary table (for example, purchases).

Examples of basic SQL queries and their logic

To give you an idea of ​​the "minimum reasonable" level, you should be able to write queries from memory such as selecting specific columns, filtering rows, and sorting results . At a very basic level, typical questions include: how to extract all columns from a table, how to select only some columns, or how to apply readable aliases with AS.

It's also common to be asked about the WHERE clause with multiple conditions combining AND, OR, and NOT, or how to use comparison operators (<, <=, >, >=, =) for both numeric values ​​and dates. Many companies emphasize NULL filters , where simply using the equals sign isn't enough; you need to use IS NULL or IS NOT NULL.

Another classic is text-based filtering with LIKE and patterns using the wildcards % and _. For example, you can find campaigns whose names contain a specific word or users whose email addresses end in a particular domain. It's important to explain that LIKE '%text%' searches for the pattern anywhere in the string.

Finally, this basic section usually covers how to update records with UPDATE and filter which rows are modified with WHERE, as well as how to delete rows with DELETE FROM using clear conditions. It's crucial that you always emphasize the importance of using a specific WHERE clause to avoid accidentally deleting half the table.

Intermediate queries: GROUP BY, HAVING, subqueries and UNION

Once you move beyond the junior level, almost every company starts to value your mastery of the combination of GROUP BY, aggregate functions, and filters on aggregates with HAVING . This is what you'll use daily to generate performance reports for campaigns, audiences, and creatives.

It works like this: GROUP BY groups rows by one or more columns, and you can apply SUM, COUNT, AVG, MIN, and MAX to these groups to calculate metrics. HAVING comes next, to keep only the groups that meet a condition, for example, customers with sales above a certain threshold.

Another recurring theme is subqueries : a query within another query. They are frequently used to filter by values ​​calculated in a previous step, such as selecting customers with sales above the global average or campaigns with impressions above the 90th percentile.

You're also often asked about the difference between UNION and UNION ALL . UNION combines two result sets with the same schema and removes duplicates; UNION ALL does the same but keeps all rows, even if they are repeated. In data analysis, you'll often prefer UNION ALL for performance reasons and because you want to preserve all the original records.

Window functions: the next leap in SQL

Window functions have become a standard in technical testing at a certain level, especially for adtech-related roles where trends over time, campaign rankings, or investment totals need to be analyzed.

The key idea is that a window function calculates a value over a set of rows related to the current row , but without collapsing the result like GROUP BY does. In other words, you still see each individual row, but accompanied by an aggregate calculated over a "window" of data.

The typical syntax is to use the function (SUM, AVG, RANK, etc.) followed by OVER, and within OVER define the partition (PARTITION BY) and the order (ORDER BY). For example, calculating the sales ranking by salesperson or the cumulative number of impressions per day.

If you want to demonstrate proficiency, you should familiarize yourself with functions like RANK, DENSE_RANK, ROW_NUMBER, LAG, and LEAD . They are very useful for ranking campaigns based on their performance, comparing each period with the previous one, or identifying year-over-year variations in key metrics.

CTE, cumulative totals and moving averages

In modern job interviews, the readability of your SQL code is highly valued. That's why you'll often be asked about Common Table Expressions (CTEs) . A CTE is essentially a type of named temporary table, defined with WITH clauses, that only exists during the execution of the query.

CTEs are used to break down complex queries into logical blocks and reuse intermediate results. For example, you first calculate daily metrics per campaign in a CTE, and then, in the main query, you perform additional aggregations or filtering on that result.

Another very common exercise is calculating a running total using SUM as a window function. In adtech environments, it's normal to be asked for cumulative impressions, clicks, or spend to see how a campaign performs over time.

Related to this are moving averages , in which AVG is applied as a window function over a shifted row frame (for example, the two previous dates and the current one). This is a simple way to smooth time series and detect trends without relying so heavily on daily peaks.

Python level typically required for data analyst

In most data analyst roles (including adtech), companies don't expect you to be a machine learning guru, but they do expect you to have a practical command of Python with pandas . Typically, you'll be asked to load datasets, clean them, transform them, and obtain simple metrics or visualizations.

Python tests typically focus on operations such as reading data from CSV files , inspecting nulls and column types, filtering rows, creating new columns, aggregations using groupby, and joins between DataFrames. In many cases, the wording is almost identical to the SQL part, but in pandas format.

It's not common to be required to build complex models from scratch in an analyst test, although they may value your ability to connect with scikit-learn to train a basic model and, above all, your knowledge of the fundamental metrics to measure its performance.

Basic pandas operations you should master

To successfully complete any Python data exercise, you must be proficient in basic DataFrames operations. The first step is usually to load a file using `read_csv` and explore its structure with methods like `head()`, `info()`, or `describe()`.

The part about cleaning up null values ​​also usually appears: counting how many nulls there are per column with isnull().sum(), deciding whether to delete entire rows or columns with dropna() or fill missing ones with fillna(), for example using the mean or median of the column.

As for filters, you'll be asked to build conditions on numeric or categorical columns, something like selecting sales above a certain amount or rows that meet several conditions combined with & and |. The key is knowing how to write df without hesitation.

The explanation of `groupby` in pandas is almost always included, as it's the direct equivalent of SQL's `GROUP BY`. You group by one or more columns and then apply aggregations like `sum`, `mean`, `count`, etc. The syntax `groupby('column').agg()` is essential.

Joins and merges between DataFrames

Just as mastering JOINs is essential in SQL, mastering pd.merge is mandatory in pandas . Companies will want to see if you know how to join datasets that share a key, for example, a table of users with another of events or purchases.

The merge function takes the two DataFrames to be joined, the key column (on), and the join type (how), which can be 'left', 'right', 'inner', or 'outer', just like in SQL. In data analysis, the left join is primarily used to preserve the main dataset and add attributes or metrics from secondary tables.

In an interview, it's a good idea to mention details like what happens when there are duplicate keys on either side or how to handle column name conflicts with parameter suffixes. This conveys a higher level of maturity.

Typical, more theoretical Python questions

Beyond pandas, many interviews include a short block of general Python questions to check your understanding of the language. These are usually short questions about features, memory, data types, or small pieces of syntax.

Among the topics that appear repeatedly is automatic memory management in Python, based on a private heap that the user does not access directly and a garbage collector that is responsible for freeing objects that no longer have references.

It's also common to be asked to compare Python with Java , not to declare a winner, but to demonstrate that you understand the differences: Python is more dynamic, with a more concise syntax, perfect for prototyping and data science, while Java tends to dominate in more corporate and high-performance ecosystems.

Other typical questions revolve around lambda expressions (anonymous functions for simple operations), pickling/unpickling processes for serializing objects to bytes and retrieving them, or the difference between lists and tuples, where the former are mutable and defined with square brackets and the latter are immutable and defined with parentheses.

More key Python concepts in interviews

It's common to be asked how to delete or copy an object in Python. Usually, it's enough to explain that you can use the `del` statement to remove a reference, and that shallow copies are done with `copy.copy()`, while deep copies require `copy.deepcopy()`.

Another concept that may appear, especially in more backend profiles, is the so-called dogpile effect , which describes the scenario in which many users or processes attack a resource (for example, a website or cache) at the same time, saturating the system.

Related to the ecosystem, you might also encounter questions about databases that can be used with Python . The most sensible approach is to mention some popular ones like MySQL, PostgreSQL, SQLite, MongoDB, and Oracle, and note that Python generally integrates well with a wide variety of relational and NoSQL database engines.

Finally, simpler questions often arise, such as how to sort a dictionary using sorted on items, what a namespace is and what it is used for (associating names with objects in different scopes), or how to launch a subprocess using the subprocess module with functions like run() or Popen().

Metrics for evaluating models in Python: the minimum you should know

Although many analyst positions won't require you to design deep learning architectures, it's common to be familiar with basic metrics for evaluating classification models , especially if the role involves data products, campaign attribution, or fraud detection in adtech.

The starting point is the confusion matrix , which summarizes the successes and failures of a binary or multiclass classifier. In the binary case, it is broken down into true positives (TP), true negatives (TN), false positives (FP), and false negatives (FN). A thorough understanding of these four categories is key.

From the matrix, metrics such as accuracy , which measures the percentage of correct predictions out of the total; precision (TP / positive predictions); and recall or sensitivity (TP / actual positives) are derived. These last two are especially important when the classes are unbalanced.

The F1 score combines accuracy and recall using the harmonic mean, penalizing particularly low scores for either. It is a common metric in scenarios where both false positives and false negatives are costly, such as fraud detection, lead scoring, or disease detection.

Other advanced metrics: ROC-AUC, logloss, Jaccard, and more

For positions with a stronger data science or marketing analytics component, companies look at whether you master more advanced metrics like ROC-AUC , which measures the area under the ROC curve and reflects a model's ability to separate classes.

The ROC curve represents the relationship between the true positive rate (recall) and the false positive rate (1 – specificity) for different decision thresholds. A random model would fall on a diagonal line, while a good model would be closer to the upper left corner. The larger the area under the curve, the better the discrimination ability.

Another common metric is log loss (logloss) , which assesses the quality of predicted probabilities, heavily penalizing overconfidence and errors. A perfect model would have a log loss of 0, and generally, the lower the log loss, the better.

They may also ask you about the Jaccard index , which measures the similarity between two sets as the size of the intersection divided by the size of the union. It is used to evaluate classifiers, segmentation, and recommendation systems, among other things.

In some contexts , gain and lift charts are mentioned , which show what percentage of targets you capture using only a portion of the population (for example, the top 20% of users rated by your model). This is widely used in marketing to decide who to target first.

Kolmogorov-Smirnov, Gini coefficient and in-depth evaluation

If the company is heavily focused on scoring or risk models, metrics such as the Kolmogorov-Smirnov (KS) statistic may appear , which measures the degree of separation between the distributions of positive and negative scores.

A KS value close to 100 (as a percentage) indicates that the model separates the two populations almost perfectly; a value close to 0 implies that the model does not distinguish any better than chance. In practice, real-world models fall within intermediate values ​​and are compared to each other to select the best one.

The Gini coefficient is another metric derived from the ROC-AUC using the formula Gini = 2 × AUC – 1. Very popular in credit and insurance, it is also interpreted as a measure of inequality: the higher the Gini, the greater the model's ability to concentrate true positives at higher scores.

In more advanced interviews, you may be asked to explain how these metrics are implemented in Python using scikit-learn (e.g., confusion_matrix, accuracy_score, roc_auc_score, f1_score…) and to comment on when you would use each one depending on the nature of the problem and the class imbalance.

How to structure your answers during the technical interview

Beyond the code you write, interviewers pay close attention to how you think and how you explain yourself . A poorly structured answer can make you seem more junior than you actually are, even if you know the correct solution.

A very useful approach for answering technical questions is as follows: first, explain the concept in one sentence , then provide a concrete example (ideally connected to one of your own projects), and, if relevant, mention alternatives or nuances . This works equally well for SQL, Python, or model metrics.

For example, if you are asked what a CTE is for, you could say that it is a named temporary subquery that improves the readability of complex queries, add that you use it when you need to reuse an intermediate result several times, and mention that in some cases it could be replaced by nested subqueries even though it is less clear.

Thinking out loud is also key . If you get stuck, don't stay silent: verbalize what you're trying to do, what information you're missing, what assumptions you're making. This helps the interviewer see your thought process and sometimes even gives you clues or clarifications that make it easier to move forward.

Most common mistakes in technical data interviews

Many candidates are eliminated not because they lack sufficient SQL or Python skills, but due to a combination of poor preparation and communication errors . It's crucial to be fully aware of common pitfalls to avoid them.

The first is memorizing without understanding . Knowing the syntax of RANK or a lambda function isn't very useful if you can't then explain in what cases you would use those tools or why they are preferable to other alternatives.

Another very common mistake is failing to assess data quality in the exercises. If you're given a dataset, before you start aggregating, it's advisable to check for nulls, duplicates, or outliers that could skew the analysis. This demonstrates sound judgment and practical experience.

It's also highly detrimental to avoid asking clarifying questions . In a business case about advertising campaigns, for example, it makes perfect sense to ask about seasonality, the target timeframe, whether metrics are being sought per user or per impression, and so on. Remaining silent and making assumptions often leads to solutions that are poorly aligned with what the interviewer had in mind.

Finally, avoid the "overcoding" approach: creating unnecessarily complex solutions when the query or script could be simpler. In real-world work environments, clarity, maintainability, and efficiency are valued , not puzzle-like solutions.

Intensive preparation plan in two weeks

If you have limited time before the interview, you can follow a condensed plan that covers the three key areas: SQL, Python with pandas, and practical application. You won't perform miracles in 14 days, but you can arrive with a solid level of proficiency and reasonable confidence.

During the first few days, it's a good idea to focus on beginner to intermediate SQL : review basic syntax, JOINs, GROUP BY, subqueries, and the most common window functions. Dedicate time to both reading examples and writing your own queries.

In a second phase, focus on pandas : data loading, cleaning, filtering, groupby, merges, and some quick visualization with matplotlib or seaborn. You don't need to build complex dashboards, but you do need to be able to replicate in Python the same transformations you would perform in SQL.

Then set aside several days to do practical exercises on platforms like HackerRank or technical interview repositories. The goal is to get used to the format, the time constraints, and the pressure of writing code in a controlled environment.

Finally, try out one or two full interview simulations : take a public dataset, ask reasonable business questions, solve them with SQL or Python, and explain your entire reasoning aloud, from the initial exploration to the final conclusions.

With a good combination of theoretical review, guided practice, and realistic exercises, you'll arrive at interview day with a solid foundation in intermediate SQL, Python for data analysis, and model metrics —which is exactly what most companies in adtech and data analytics expect to see.