Medium Data Analytics Interview Questions

SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1; use ROW_NUMBER() partitioned by the key to delete extras.

-- A2:D500 holds Order, Customer, Region, Amount =FILTER(A2:D500, C2:C500="North", "No orders") -- two conditions (AND) multiply the tests =FILTER(A2:D500, (C2:C500="North")*(D2:D500>10000), "No orders") -- OR conditions add the tests =FILTER(A2:D500, (C2:C500="North")+(C2:C500="East"))

-- C2 = invoice date, TODAY() = as-of date =LET(age, TODAY()-C2, IFS(age<=30, "0-30", age<=60, "31-60", age<=90, "61-90", TRUE, "90+")) -- then a PivotTable with bucket in rows and SUM of amount gives the ageing report

Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) Sales YoY % = VAR cur = [Total Sales] VAR prev = [Sales LY] RETURN DIVIDE(cur - prev, prev) -- needs a marked date table; format the measure as a percentage

Cumulative Sales = VAR maxDate = MAX('Date'[Date]) RETURN CALCULATE( [Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date] <= maxDate) ) -- for a running total that resets each year use TOTALYTD([Total Sales], 'Date'[Date])

import pandas as pd orders = pd.read_csv('orders.csv', parse_dates=['order_date']) orders['revenue'] = orders['qty'] * orders['unit_price'] monthly = ( orders .groupby(orders['order_date'].dt.to_period('M'))['revenue'] .sum() .reset_index(name='revenue') ) print(monthly)

import pandas as pd summary = pd.pivot_table( df, index='region', columns='category', values='sales', aggfunc=['sum', 'count'], fill_value=0, margins=True # adds an All row and column ) print(summary)

df = df.sort_values(['store', 'month']) df['mom_pct'] = ( df.groupby('store')['sales'].pct_change() * 100 ).round(1) df['cum_sales'] = df.groupby('store')['sales'].cumsum() # first month of each store has NaN for mom_pct, which is expected

from pathlib import Path import pandas as pd def combine_excel(folder): frames = [] for f in Path(folder).glob('*.xlsx'): df = pd.read_excel(f) df['source_file'] = f.name frames.append(df) return pd.concat(frames, ignore_index=True) all_sales = combine_excel('monthly_reports')

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;

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

SELECT region, SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1, SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2, SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3, SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4 FROM sales GROUP BY region; -- conditional aggregation works in every database; some also offer PIVOT

// First Purchase Date { FIXED [Customer ID] : MIN([Order Date]) } // Order Type IF [Order Date] = [First Purchase Date] THEN 'New' ELSE 'Repeat' END // Cohort Month for retention analysis DATETRUNC('month', [First Purchase Date])

// with YEAR(Order Date) in the view, Compute Using: Table (across) (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1)) // the built-in Quick Table Calculation 'Year over Year Growth' generates similar logic // format the field as a percentage

city_map = { 'delhi': 'Delhi', 'new delhi': 'Delhi', 'bombay': 'Mumbai', 'mumbai': 'Mumbai', 'bengaluru': 'Bengaluru', 'bangalore': 'Bengaluru', } clean = df['city'].str.strip().str.lower().str.replace(r'\s+', ' ', regex=True) df['city_clean'] = clean.map(city_map).fillna(clean.str.title()) print(df['city_clean'].value_counts())

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

import re pattern = re.compile(r'(?:\+?91[\s-]?|0)?([6-9]\d{9})') def get_mobile(text): if not isinstance(text, str): return None digits = re.sub(r'[\s()-]', '', text) m = pattern.search(digits) return m.group(1) if m else None df['mobile'] = df['contact'].apply(get_mobile)

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

-- 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;

=XLOOKUP(1, (A2:A500=G2) * (B2:B500=H2), C2:C500, "Not found") -- A = product, B = city, C = price -- G2 and H2 hold the product and city to find -- each condition returns TRUE/FALSE; multiplying gives 1 only where both match

top2 = ( df.groupby(['region', 'product'], as_index=False)['revenue'] .sum() .sort_values(['region', 'revenue'], ascending=[True, False]) .groupby('region') .head(2) ) print(top2)

-- Works in PostgreSQL, Snowflake, SQL Server (with OVER for SQL Server) SELECT city, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) AS median_value, AVG(order_value) AS mean_value FROM orders GROUP BY city ORDER BY median_value DESC;

from statsmodels.stats.power import NormalIndPower from statsmodels.stats.proportion import proportion_effectsize baseline = 0.04 target = baseline * 1.10 # 4.4% effect = proportion_effectsize(target, baseline) n = NormalIndPower().solve_power( effect_size=effect, alpha=0.05, power=0.8, alternative='two-sided' ) print(round(n)) # roughly 39,000 users per group

from statsmodels.stats.proportion import proportions_ztest, proportion_confint conversions = [1180, 1295] # control, treatment visitors = [30000, 30100] z, p = proportions_ztest(conversions, visitors) print(f'z = {z:.2f}, p = {p:.4f}') for c, n in zip(conversions, visitors): low, high = proportion_confint(c, n, method='wilson') print(f'rate {c/n:.3%} 95% CI {low:.3%} to {high:.3%}')

// Calculated field: Last 30 Days Flag DATEDIFF('day', [Order Date], TODAY()) <= 29 AND [Order Date] <= TODAY() // Put this field on Filters and keep True. // For a sales measure instead of a filter: // IF DATEDIFF('day', [Order Date], TODAY()) <= 29 THEN [Sales] END

# 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())

WITH touches AS ( SELECT t.user_id, t.channel, t.touch_ts, ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.touch_ts) AS first_rank, ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.touch_ts DESC) AS last_rank FROM marketing_touches t JOIN conversions c ON c.user_id = t.user_id AND t.touch_ts <= c.converted_ts ) SELECT user_id, MAX(CASE WHEN first_rank = 1 THEN channel END) AS first_touch, MAX(CASE WHEN last_rank = 1 THEN channel END) AS last_touch FROM touches GROUP BY user_id;

import pandas as pd from urllib.parse import urlparse, parse_qs def get_utms(url): params = parse_qs(urlparse(str(url)).query) return pd.Series({ 'source': params.get('utm_source', ['(direct)'])[0].lower(), 'medium': params.get('utm_medium', ['(none)'])[0].lower(), 'campaign': params.get('utm_campaign', ['(not set)'])[0].lower(), }) df[['source', 'medium', 'campaign']] = df['landing_url'].apply(get_utms)

SELECT s.campaign_id, COUNT(DISTINCT s.user_id) AS delivered, COUNT(DISTINCT o.user_id) AS opened, COUNT(DISTINCT c.user_id) AS clicked, ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT s.user_id), 1) AS open_rate, ROUND(100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT s.user_id), 1) AS ctr FROM email_sends s LEFT JOIN email_opens o ON o.campaign_id = s.campaign_id AND o.user_id = s.user_id LEFT JOIN email_clicks c ON c.campaign_id = s.campaign_id AND c.user_id = s.user_id WHERE s.status = 'delivered' GROUP BY s.campaign_id;

# spend: channel, spend # customers: channel, customer_id, monthly_margin new_cust = customers.groupby('channel').agg( new_customers=('customer_id', 'nunique'), avg_margin=('monthly_margin', 'mean')) summary = spend.groupby('channel')['spend'].sum().to_frame().join(new_cust) summary['cac'] = summary['spend'] / summary['new_customers'] summary['payback_months'] = summary['cac'] / summary['avg_margin'] print(summary.round(1).sort_values('payback_months'))

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

df['date'] = pd.to_datetime(df['date']) daily = (df.groupby('date')['sales'].sum() .asfreq('D', fill_value=0)) # adds missing days as 0 weekly = daily.resample('W-MON', label='left', closed='left').sum() print(weekly.head()) # W-MON with label/closed left gives weeks starting on Monday