Data Analytics Interview Questions in Coimbatore

-- B = Spend, C = Conversions, D = Revenue, target ROAS in H1 E2 (ROAS): =IFERROR(D2/B2, 0) F2 (CPA): =IF(C2=0, "No conversions", B2/C2) G2 (Flag): =IF(E2<$H$1, "Below target", "OK") -- Copy down, then add conditional formatting on G to highlight 'Below target'

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)

import pandas as pd import statsmodels.formula.api as smf df = pd.read_csv('marketing.csv') model = smf.ols('sales ~ ad_spend', data=df).fit() print(model.summary()) # coefficients, p-values, R-squared # predicted sales for a spend of 50,000 print(model.predict(pd.DataFrame({'ad_spend': [50000]})))

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

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;

WITH active AS ( SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS month FROM orders ) SELECT a.month, COUNT(*) AS active_customers, COUNT(*) - COUNT(n.customer_id) AS churned_next_month, ROUND(100.0 * (COUNT(*) - COUNT(n.customer_id)) / COUNT(*), 1) AS churn_pct FROM active a LEFT JOIN active n ON n.customer_id = a.customer_id AND n.month = a.month + INTERVAL '1 month' GROUP BY a.month ORDER BY a.month;

WITH dau AS ( SELECT CAST(event_ts AS DATE) AS day, COUNT(DISTINCT user_id) AS dau FROM events GROUP BY CAST(event_ts AS DATE) ) SELECT d.day, d.dau, (SELECT COUNT(DISTINCT e.user_id) FROM events e WHERE CAST(e.event_ts AS DATE) BETWEEN d.day - INTERVAL '29 days' AND d.day) AS mau, ROUND(100.0 * d.dau / (SELECT COUNT(DISTINCT e.user_id) FROM events e WHERE CAST(e.event_ts AS DATE) BETWEEN d.day - INTERVAL '29 days' AND d.day), 1) AS stickiness_pct FROM dau d ORDER BY d.day;

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

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')