A SQL analyst writes and maintains queries that extract, join and aggregate data from databases and warehouses for reports, ad hoc requests and data checks. Interviews focus heavily on live SQL problems: joins, aggregation, subqueries, CTEs, window functions, date logic and NULL handling, plus reading execution plans and avoiding duplicate counting. Clean, readable and correct queries matter more than clever tricks.
Complex joins and CTEs Window functions Query optimisation basics Data validation queries Date and NULL handling
Solved for this roleStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
A chi-square test of independence checks whether two categorical variables are related, using a contingency table of counts. Example: is preferred payment method related to city tier? It compares observed counts with the counts expected if there were no relationship. Use it with counts, not percentages, and make sure expected counts are not too small, roughly at least 5 per cell. A significant result shows association, not causation.
02
Write a DAX measure for a KPI card showing achievement against target and a colour for conditional formatting.
Achievement % =
DIVIDE([Total Sales], [Sales Target])
KPI Colour =
VAR a = [Achievement %]
RETURN
SWITCH(
TRUE(),
ISBLANK(a), "#9E9E9E",
a >= 1, "#2E7D32",
a >= 0.9, "#F9A825",
"#C62828"
)
-- apply KPI Colour via Format > Callout value > fx > Field value
IN checks whether a value is in a list or subquery result; EXISTS checks whether a subquery returns at least one row and stops at the first match. Results are the same in most cases, and modern optimisers often run them similarly. The big difference is NOT IN with NULLs: if the subquery returns any NULL, NOT IN returns no rows. NOT EXISTS behaves correctly, so prefer it for anti-joins.
05
Write SQL to generate a simple date dimension for 2026.
-- PostgreSQL syntax
CREATE TABLE dim_date AS
SELECT
TO_CHAR(d, 'YYYYMMDD')::INT AS date_key,
d::DATE AS full_date,
EXTRACT(YEAR FROM d) AS year,
EXTRACT(QUARTER FROM d) AS quarter,
EXTRACT(MONTH FROM d) AS month_no,
TO_CHAR(d, 'Mon') AS month_name,
TO_CHAR(d, 'Dy') AS weekday,
EXTRACT(ISODOW FROM d) IN (6, 7) AS is_weekend
FROM GENERATE_SERIES(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS d;
06
After joining orders to payments, your revenue total doubles. What went wrong?
This is a join fan-out: an order has more than one matching payment row, for example split payments or retries, so each order line is repeated and sums are inflated. Check by counting rows per key on both sides. Fix it by aggregating payments to one row per order before joining, joining at the correct grain, or using the right key. Always compare row counts and totals before and after a join.
Situation: our team moved 15 Excel and older BI reports into Tableau. Task: I owned the sales set. Action: I listed every metric with its definition, rebuilt the shared data source first, then the dashboards, and ran side-by-side number checks for two weeks with the old reports. I held short demos for users. Result: all reports went live with matching numbers, and refresh effort fell from a day to an automatic schedule.
09
Tell me about a time a stakeholder rejected your first dashboard design.
Situation: I built a Tableau operations dashboard, and the warehouse head said it was too complex to use on the floor. Task: make it useful for them. Action: I watched how supervisors worked, learned they needed three numbers on a tablet every hour, and rebuilt it with large KPI tiles and one drill-down. Result: daily usage went from a handful of users to every shift supervisor within two weeks.
10
An A/B test shows a 2% lift with p = 0.04, but it only ran for 3 days. Would you ship it?
Not yet. Three days misses weekly patterns, and early results are often inflated by novelty effects or by stopping once the p-value looked good. I would check whether the planned sample size was reached, whether the split was balanced, and how guardrail metrics behaved. I would recommend running to the pre-planned duration, at least one or two full weeks, and then deciding, considering the cost of being wrong.
11
Sales data from different countries mixes currencies and some weights are in grams while others are in kilograms. How do you handle it?
Never add mixed units. Identify the unit for each row from a currency or unit column, or infer it from the source system. Convert to one standard, for example INR using a dated exchange rate table joined on transaction date, and kilograms using a conversion factor. Keep the original value and unit columns for audit. Document which rates were used, and flag rows where the unit could not be determined.
12
An inner join between orders and customers returns fewer orders than the orders table has. What could be wrong?
Orphan records are child rows whose foreign key has no matching parent, such as orders with a customer_id that does not exist in the customer table. They break joins silently: an inner join drops them and totals fall short. Find them with a LEFT JOIN from the child to the parent and filter where the parent key IS NULL. Then fix the source, add an Unknown member, or report them separately.
You assume no effect (null), collect evidence and decide whether the data is surprising enough to reject that assumption at a chosen significance level.
14
What is referential integrity and how do orphan records appear in analytics data?
Referential integrity means every foreign key value points to an existing row in the parent table, for example every order's customer_id exists in customers. Orphan records break this rule. They appear when source systems load tables at different times, records are deleted without cascading, or IDs are formatted differently across systems. Detect them with a LEFT JOIN where the parent key is NULL, and decide whether to fix, map or flag them.
15
Write SQL to find the number of days between each customer's consecutive orders and their average gap.
WITH gaps AS (
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
)
SELECT customer_id,
COUNT(*) AS orders,
AVG(days_since_prev) AS avg_gap_days
FROM gaps
GROUP BY customer_id
HAVING COUNT(*) > 1;
-- order_date is DATE (PostgreSQL); use DATEDIFF in other databases
16
Write pandas code to fill missing values in a column with the median of each group.
# Fill missing delivery_days with the median for that city
df['delivery_days'] = (
df['delivery_days']
.fillna(df.groupby('city')['delivery_days'].transform('median'))
)
# Rows in a city with no values at all stay NaN; fall back to overall median
df['delivery_days'] = df['delivery_days'].fillna(df['delivery_days'].median())
17
Write SQL to show each category's percentage share of total revenue.
SELECT category,
SUM(amount) AS revenue,
ROUND(100.0 * SUM(amount)
/ SUM(SUM(amount)) OVER (), 2) AS pct_share
FROM sales
GROUP BY category
ORDER BY revenue DESC;
-- SUM(SUM(amount)) OVER () is the grand total across all groups
18
How would you find customers who never placed an order?
Table calculations are computed on the aggregated results already in the view, not on the underlying rows. Examples are running total, percent of total, difference from previous, rank and moving average. Their result depends on the direction and partition you set with Compute Using, for example table across or by specific dimensions. They are quick to add from Quick Table Calculation, but incorrect Compute Using settings are a frequent cause of wrong numbers.
20
You inherit a monthly Excel report and suspect some numbers are hard-typed over formulas. How do you audit it?
Use Go To Special and select Constants with Numbers to highlight hard-typed values inside formula areas. Show Formulas (Ctrl+`) to scan columns for inconsistencies, and use Trace Precedents and Dependents on key totals. Inquire or Spreadsheet Compare, if available, can flag inconsistent formulas. Then fix the overwritten cells, protect formula ranges and add check totals that compare with the source data each month.
WITH d AS (
SELECT DISTINCT user_id, CAST(login_time AS DATE) AS dt
FROM logins
),
g AS (
SELECT user_id, dt,
dt - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS INT) AS grp
FROM d
)
SELECT DISTINCT user_id
FROM g
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
-- gaps and islands: consecutive dates share the same grp value
COALESCE returns the first non-null value from its arguments, for example COALESCE(phone, alt_phone, 'Not given') fills missing phone numbers. NULLIF(a, b) returns NULL when a equals b, otherwise a. Its classic use is avoiding division by zero: revenue / NULLIF(orders, 0) returns NULL instead of an error when orders is zero. Together they handle most null-related reporting problems cleanly.
26
Tell me about a time you used data to challenge an assumption.
Situation: our team believed weekend discounts drove most sales. Task: I was preparing the monthly review. Action: I compared discount and non-discount weekends over six months and controlled for paydays and festivals. Discounts moved sales forward from the following week but added little overall. I presented it with one clear chart. Result: management trialled fewer discount weekends, and margin improved without a fall in monthly revenue.
This is an encoding mismatch: a UTF-8 file was read as a different encoding such as Latin-1 or Windows-1252. Re-read it with the right encoding, for example pd.read_csv(path, encoding='utf-8') or 'utf-8-sig' for files with a byte order mark, or set the file origin in Excel and Power Query import. Avoid re-saving through tools that change encoding, and agree on UTF-8 for all exchanges, especially for Hindi or other Indian language text.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct
FROM monthly
ORDER BY month;
30
How would you design an A/B test for a new checkout button?
Define one primary metric, such as checkout conversion, plus guardrails like refund rate. State the hypothesis and minimum detectable effect, then calculate sample size and duration for chosen power and significance. Randomise users, not sessions, into control and variant, check the split is balanced, and run for full weeks to cover weekday patterns. Do not peek and stop early. Finally analyse with a proportion test and check key segments.
A confidence interval gives a range of plausible values for a population parameter based on a sample. A 95 percent interval for average order value of 820 to 880 rupees means that if we repeated the sampling many times, about 95 percent of such intervals would contain the true average. Wider intervals mean more uncertainty; larger samples narrow them. Reporting the interval is more honest than reporting one number.
32
When would you drop missing values, and when would you impute with mean or median?
Drop rows when few are missing, roughly under 5 percent, and missingness looks random. Impute when dropping would lose too much data. Use the median for skewed numbers such as income or order value, the mean for roughly symmetric data, and the mode or an Unknown category for categoricals. Group-wise imputation, such as median by city, is better than one global value. Always add a flag column showing what was imputed.
33
What is the Central Limit Theorem and why does it matter for analysts?
The Central Limit Theorem says that the distribution of sample means approaches a normal distribution as sample size grows, usually from about 30 observations, even if the underlying data is skewed. This is why we can use z-tests, t-tests and confidence intervals for averages such as order value or session time. It does not make the raw data normal, and very heavy-tailed data may need larger samples.
ROW_NUMBER gives a unique sequence. RANK gives ties the same rank and skips the next (1, 1, 3). DENSE_RANK gives ties the same rank without gaps (1, 1, 2).
37
What is the difference between a t-test and a z-test?
Both compare means. A z-test assumes the population standard deviation is known or the sample is large, so the normal distribution applies. A t-test estimates standard deviation from the sample and uses the t-distribution, which has heavier tails for small samples. In practice analysts mostly use t-tests: one-sample, independent two-sample (Welch's version if variances differ) or paired, for before-after measurements on the same users.
Simpson's paradox happens when a trend in several groups reverses when the groups are combined, because group sizes differ. Example: a new sales process has higher conversion in both small and large stores, yet overall conversion looks lower because it was mostly rolled out to large stores, which convert worse in general. The lesson is to check segments and confounding variables before concluding from aggregate numbers.
39
Write SQL to flag rows that fail basic validation rules in a customer table.
SELECT customer_id,
CASE WHEN email IS NULL OR email NOT LIKE '%_@_%._%' THEN 1 ELSE 0 END AS bad_email,
CASE WHEN LENGTH(pincode) <> 6 THEN 1 ELSE 0 END AS bad_pincode,
CASE WHEN signup_date > CURRENT_DATE THEN 1 ELSE 0 END AS future_signup,
CASE WHEN age NOT BETWEEN 18 AND 100 THEN 1 ELSE 0 END AS bad_age
FROM customers;
-- sum each flag to report the error rate per rule
40
Estimate how many cups of tea are sold in a city of 1 crore people in a day.
State assumptions clearly. Of 1 crore people, assume 70 percent drink tea, so 70 lakh. Say half of their cups are bought outside, at an average of one bought cup per drinker every day for that half, giving about 35 lakh purchased cups, with more at offices and stations. So roughly 30 to 40 lakh cups. Interviewers judge the structure, segmentation and sanity check more than the exact number.
Frequently asked questions
What does a SQL Analyst do?
A SQL analyst writes and maintains queries that extract, join and aggregate data from databases and warehouses for reports, ad hoc requests and data checks. Interviews focus heavily on live SQL problems: joins, aggregation, subqueries, CTEs, window functions, date logic and NULL handling, plus reading execution plans and avoiding duplicate counting. Clean, readable and correct queries matter more than clever tricks.
Which skills are tested in a SQL Analyst interview?
Expect questions on Complex joins and CTEs, Window functions, Query optimisation basics, Data validation queries, Date and NULL handling. Most rounds also ask you to explain a project and solve one practical problem live.
How do I prepare for a SQL Analyst interview?
Revise SQL, Data Cleaning, Statistics and Case Studies first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.