Zomato Data Analyst Interview Questions and Answers 2026

WITH restaurant_stats AS (
    SELECT
        o.restaurant_id,
        r.city,
        COUNT(CASE WHEN o.status = 'placed' THEN 1 END)     AS total_placed,
        COUNT(CASE WHEN o.status = 'delivered' THEN 1 END)  AS total_delivered,
        AVG(CASE WHEN o.status = 'delivered' THEN o.rating END) AS avg_rating,
        COUNT(CASE WHEN o.status = 'delivered'
                    AND o.prep_time_mins < 20 THEN 1 END)   AS on_time_count
    FROM orders o
    JOIN restaurants r ON o.restaurant_id = r.restaurant_id
    GROUP BY o.restaurant_id, r.city
),
health_scores AS (
    SELECT
        restaurant_id,
        city,
        total_delivered,
        avg_rating,
        ROUND(
            0.40 * (100.0 * total_delivered / NULLIF(total_placed, 0))
          + 0.30 * (COALESCE(avg_rating, 0) * 20)
          + 0.30 * (100.0 * on_time_count / NULLIF(total_delivered, 0)),
        2) AS health_score
    FROM restaurant_stats
)
SELECT
    restaurant_id,
    city,
    total_delivered,
    ROUND(avg_rating, 2)  AS avg_rating,
    health_score,
    RANK() OVER (PARTITION BY city ORDER BY health_score DESC) AS city_rank
FROM health_scores
ORDER BY city, city_rank;

-- Part (a): Orders per hexagon per hour-of-week slot
WITH hexagon_demand AS (
    SELECT
        FLOOR(lat  / 0.0045) AS hex_lat,
        FLOOR(long / 0.0045) AS hex_lon,
        EXTRACT(DOW  FROM order_datetime) AS day_of_week,   -- 0=Sun
        EXTRACT(HOUR FROM order_datetime) AS hour_of_day,
        (EXTRACT(DOW FROM order_datetime) * 24
          + EXTRACT(HOUR FROM order_datetime))::INT         AS hour_of_week_slot,
        COUNT(order_id)  AS order_count,
        SUM(gmv)         AS total_gmv
    FROM orders
    GROUP BY hex_lat, hex_lon, day_of_week, hour_of_day, hour_of_week_slot
),
-- Part (b): Top 20 hexagons by evening peak demand (18:00–20:59)
evening_peak AS (
    SELECT
        hex_lat,
        hex_lon,
        SUM(order_count) AS evening_orders
    FROM hexagon_demand
    WHERE hour_of_day BETWEEN 18 AND 20
    GROUP BY hex_lat, hex_lon
    ORDER BY evening_orders DESC
    LIMIT 20
),
-- Part (c): High-demand hexagons with no restaurant within 1.5 km
-- 1 degree lat ~ 111 km, 1 degree lon ~ 111 km * cos(lat)
-- 1.5 km ~ 0.0135 degrees
hex_centers AS (
    SELECT
        hex_lat,
        hex_lon,
        (hex_lat + 0.5) * 0.0045 AS center_lat,
        (hex_lon + 0.5) * 0.0045 AS center_lon,
        SUM(order_count)          AS total_orders
    FROM hexagon_demand
    GROUP BY hex_lat, hex_lon
),
hex_with_restaurant AS (
    SELECT DISTINCT h.hex_lat, h.hex_lon
    FROM hex_centers h
    JOIN restaurants r
      ON ABS(h.center_lat - r.lat) < 0.0135
     AND ABS(h.center_lon - r.long) < 0.0135
)
SELECT
    h.hex_lat,
    h.hex_lon,
    h.total_orders,
    h.center_lat,
    h.center_lon
FROM hex_centers h
LEFT JOIN hex_with_restaurant hwr
  ON h.hex_lat = hwr.hex_lat AND h.hex_lon = hwr.hex_lon
WHERE hwr.hex_lat IS NULL
  AND h.total_orders > (SELECT PERCENTILE_CONT(0.75)
                          WITHIN GROUP (ORDER BY total_orders)
                        FROM hex_centers)
ORDER BY h.total_orders DESC;

WITH first_order AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
user_activity AS (
    SELECT
        o.user_id,
        f.cohort_month,
        DATE_TRUNC('month', o.order_date) AS activity_month,
        DATEDIFF('month', f.cohort_month,
                 DATE_TRUNC('month', o.order_date)) AS months_after
    FROM orders o
    JOIN first_order f ON o.user_id = f.user_id
    WHERE DATEDIFF('month', f.cohort_month,
                  DATE_TRUNC('month', o.order_date)) BETWEEN 0 AND 3
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM first_order
    GROUP BY cohort_month
),
retention_counts AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT CASE WHEN months_after = 0 THEN user_id END) AS m0,
        COUNT(DISTINCT CASE WHEN months_after = 1 THEN user_id END) AS m1,
        COUNT(DISTINCT CASE WHEN months_after = 2 THEN user_id END) AS m2,
        COUNT(DISTINCT CASE WHEN months_after = 3 THEN user_id END) AS m3
    FROM user_activity
    GROUP BY cohort_month
)
SELECT
    r.cohort_month,
    cs.cohort_size,
    ROUND(100.0 * r.m0 / NULLIF(cs.cohort_size, 0), 1) AS month_0_pct,
    ROUND(100.0 * r.m1 / NULLIF(cs.cohort_size, 0), 1) AS month_1_pct,
    ROUND(100.0 * r.m2 / NULLIF(cs.cohort_size, 0), 1) AS month_2_pct,
    ROUND(100.0 * r.m3 / NULLIF(cs.cohort_size, 0), 1) AS month_3_pct
FROM retention_counts r
JOIN cohort_sizes cs ON r.cohort_month = cs.cohort_month
ORDER BY r.cohort_month;

-- Part (a): Top 3 cuisines by order count per city
WITH city_cuisine_orders AS (
    SELECT
        r.city,
        r.cuisine_type,
        COUNT(o.order_id) AS order_count,
        RANK() OVER (PARTITION BY r.city ORDER BY COUNT(o.order_id) DESC) AS rnk
    FROM orders o
    JOIN restaurants r ON o.restaurant_id = r.restaurant_id
    GROUP BY r.city, r.cuisine_type
)
SELECT city, cuisine_type, order_count, rnk
FROM city_cuisine_orders
WHERE rnk <= 3
ORDER BY city, rnk;

-- Part (b): Power users — 5+ distinct cuisine types in last 6 months
SELECT
    o.user_id,
    COUNT(DISTINCT r.cuisine_type) AS cuisine_variety
FROM orders o
JOIN restaurants r ON o.restaurant_id = r.restaurant_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY o.user_id
HAVING COUNT(DISTINCT r.cuisine_type) >= 5
ORDER BY cuisine_variety DESC;

-- Part (c): Most common cuisine pairs ordered by same user on same day
WITH user_day_cuisines AS (
    SELECT
        o.user_id,
        o.order_date,
        r.cuisine_type
    FROM orders o
    JOIN restaurants r ON o.restaurant_id = r.restaurant_id
),
cuisine_pairs AS (
    SELECT
        a.user_id,
        a.cuisine_type AS cuisine_a,
        b.cuisine_type AS cuisine_b
    FROM user_day_cuisines a
    JOIN user_day_cuisines b
      ON a.user_id    = b.user_id
     AND a.order_date = b.order_date
     AND a.cuisine_type < b.cuisine_type   -- avoid duplicates, enforce ordering
)
SELECT
    cuisine_a,
    cuisine_b,
    COUNT(*) AS pair_count
FROM cuisine_pairs
GROUP BY cuisine_a, cuisine_b
ORDER BY pair_count DESC
LIMIT 20;

WITH order_segment AS (
    -- Tag each order with whether the user was Gold at time of order
    SELECT
        o.order_id,
        o.user_id,
        o.order_date,
        o.gmv,
        o.discount,
        CASE
            WHEN u.subscription_type = 'Gold'
             AND o.order_date BETWEEN u.subscription_start
                                  AND COALESCE(u.subscription_end, CURRENT_DATE)
            THEN 'Gold'
            ELSE 'Non-Gold'
        END AS segment
    FROM orders o
    JOIN users u ON o.user_id = u.user_id
),
monthly_orders AS (
    SELECT
        user_id,
        segment,
        DATE_TRUNC('month', order_date) AS order_month,
        COUNT(order_id)                 AS monthly_order_count
    FROM order_segment
    GROUP BY user_id, segment, order_month
),
avg_monthly AS (
    SELECT
        segment,
        AVG(monthly_order_count) AS avg_orders_per_month
    FROM monthly_orders
    GROUP BY segment
),
overall_metrics AS (
    SELECT
        segment,
        AVG(gmv)                                          AS avg_gmv_per_order,
        AVG(CASE WHEN discount > 0 THEN 1.0 ELSE 0 END)  AS discount_utilization_rate
    FROM order_segment
    GROUP BY segment
),
churn AS (
    SELECT
        u.user_id,
        CASE
            WHEN u.subscription_type = 'Gold'
            THEN 'Gold' ELSE 'Non-Gold'
        END AS segment,
        CASE
            WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '60 days'
              OR MAX(o.order_date) IS NULL
            THEN 1 ELSE 0
        END AS is_churned
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id
    GROUP BY u.user_id, segment
),
churn_rate AS (
    SELECT segment, AVG(is_churned) AS churn_rate
    FROM churn
    GROUP BY segment
)
SELECT
    am.segment,
    ROUND(am.avg_orders_per_month, 2)            AS avg_orders_per_month,
    ROUND(om.avg_gmv_per_order, 2)               AS avg_gmv_per_order,
    ROUND(om.discount_utilization_rate * 100, 1) AS discount_util_pct,
    ROUND(cr.churn_rate * 100, 1)                AS churn_rate_pct
FROM avg_monthly am
JOIN overall_metrics om ON am.segment = om.segment
JOIN churn_rate cr      ON am.segment = cr.segment;

import pandas as pd
import re

def clean_reviews(filepath: str) -> tuple[pd.DataFrame, dict]:
    """Load, audit, and clean restaurant reviews CSV."""
    df = pd.read_csv(filepath)
    original_len = len(df)
    report = {}
    # ── 1. Duplicate review_id ──────────────────────────
    dupes = df.duplicated(subset='review_id', keep='first').sum()
    df = df.drop_duplicates(subset='review_id', keep='first')
    report['duplicate_review_ids_removed'] = int(dupes)
    # ── 2. Rating out of range ──────────────────────────
    invalid_rating = (~df['rating'].between(1, 5)).sum()
    df = df[df['rating'].between(1, 5)].copy()
    report['invalid_ratings_removed'] = int(invalid_rating)
    # ── 3. Low-quality review text ──────────────────────
    # Emoji-only: no ASCII letters; single-word: no whitespace after strip
    EMOJI_PATTERN = re.compile(
        r'^[\U00010000-\U0010ffff\U00002600-\U000027BF\U0001F300-\U0001F9FF\s]+$'
    )
    def is_low_quality(text) -> bool:
        if pd.isna(text):
            return True
        text = str(text).strip()
        if len(text.split()) <= 1:
            return True
        if EMOJI_PATTERN.match(text):
            return True
        return False
    low_quality_mask = df['review_text'].apply(is_low_quality)
    low_quality_count = low_quality_mask.sum()
    df = df[~low_quality_mask].copy()
    report['low_quality_reviews_removed'] = int(low_quality_count)
    # ── 4. Missing reviewer_id ──────────────────────────
    null_reviewer = df['reviewer_id'].isna().sum()
    df = df.dropna(subset=['reviewer_id']).copy()
    report['null_reviewer_ids_removed'] = int(null_reviewer)
    # ── 5. Mixed date formats ────────────────────────────
    def parse_date(val):
        if pd.isna(val):
            return pd.NaT
        # Try ISO first, then DD/MM/YYYY
        for fmt in ('%Y-%m-%dT%H:%M:%S', '%Y-%m-%d', '%d/%m/%Y'):
            try:
                return pd.to_datetime(val, format=fmt)
            except (ValueError, TypeError):
                pass
        return pd.NaT
    df['created_at'] = df['created_at'].apply(parse_date)
    unparseable_dates = df['created_at'].isna().sum()
    df = df.dropna(subset=['created_at']).copy()
    report['unparseable_dates_removed'] = int(unparseable_dates)
    report['original_rows'] = original_len
    report['clean_rows'] = len(df)
    report['total_rows_removed'] = original_len - len(df)
    df = df.reset_index(drop=True)
    return df, report

# Usage
clean_df, quality_report = clean_reviews('restaurant_reviews.csv')
print(quality_report)

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Ensure order_date is datetime
df['order_date'] = pd.to_datetime(df['order_date'])
# ── (a) Cohort month: user's first order month ──────────
df['order_period'] = df['order_date'].dt.to_period('M')
df['cohort_month'] = (
    df.groupby('user_id')['order_period']
      .transform('min')
)
# ── (b) Cohort age: months since cohort month ───────────
df['cohort_age'] = (df['order_period'] - df['cohort_month']).apply(lambda x: x.n)
# ── (c) Cohort sizes and retention pivot ────────────────
cohort_sizes = (
    df[df['cohort_age'] == 0]
      .groupby('cohort_month')['user_id']
      .nunique()
      .rename('cohort_size')
)
retention_counts = (
    df.groupby(['cohort_month', 'cohort_age'])['user_id']
      .nunique()
      .reset_index()
      .rename(columns={'user_id': 'active_users'})
)
retention_counts = retention_counts.merge(cohort_sizes, on='cohort_month')
retention_counts['retention_rate'] = (
    retention_counts['active_users'] / retention_counts['cohort_size'] * 100
).round(1)
cohort_pivot = retention_counts.pivot_table(
    index='cohort_month',
    columns='cohort_age',
    values='retention_rate'
)
cohort_pivot.index = cohort_pivot.index.astype(str)
# ── (d) Heatmap ─────────────────────────────────────────
fig, ax = plt.subplots(figsize=(12, 6))
sns.heatmap(
    cohort_pivot,
    annot=True,
    fmt='.1f',
    cmap='YlOrRd_r',
    linewidths=0.5,
    linecolor='#f1f5f9',
    cbar_kws={'label': 'Retention Rate (%)'},
    ax=ax
)
ax.set_title('Zomato User Cohort Retention (Monthly)', fontsize=14, fontweight='bold', pad=15)
ax.set_xlabel('Months Since First Order', fontsize=11)
ax.set_ylabel('Cohort Month', fontsize=11)
plt.tight_layout()
plt.savefig('cohort_retention_heatmap.png', dpi=150, bbox_inches='tight')
plt.show()
print(cohort_pivot)

import pandas as pd
import numpy as np
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import LabelEncoder
from sklearn.metrics import mean_absolute_error

# ── Feature engineering ──────────────────────────────────
# df columns: cuisine_type, avg_prep_time, num_items_on_menu,
#             city_tier, avg_order_value, has_veg_only,
#             weekend_rush_ratio, avg_rating (target)

CATEGORICAL_COLS = ['cuisine_type', 'city_tier']
BOOLEAN_COLS     = ['has_veg_only']
NUMERIC_COLS     = ['avg_prep_time', 'num_items_on_menu',
                    'avg_order_value', 'weekend_rush_ratio']
TARGET           = 'avg_rating'

df_model = df.copy()
# Encode categoricals with LabelEncoder
le = {}
for col in CATEGORICAL_COLS:
    le[col] = LabelEncoder()
    df_model[col] = le[col].fit_transform(df_model[col].astype(str))
# Boolean: convert True/False to 1/0
for col in BOOLEAN_COLS:
    df_model[col] = df_model[col].astype(int)
FEATURES = CATEGORICAL_COLS + BOOLEAN_COLS + NUMERIC_COLS
X = df_model[FEATURES]
y = df_model[TARGET]
# ── Train-test split ─────────────────────────────────────
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)
# ── Model training ───────────────────────────────────────
# Random Forest — no feature scaling needed
rf = RandomForestRegressor(
    n_estimators=200,
    max_depth=8,
    min_samples_leaf=5,
    random_state=42,
    n_jobs=-1
)
rf.fit(X_train, y_train)
# ── Evaluation ───────────────────────────────────────────
y_pred = rf.predict(X_test)
mae  = mean_absolute_error(y_test, y_pred)
# MAPE: avoid division by zero (ratings are always >= 1)
mape = np.mean(np.abs((y_test - y_pred) / y_test)) * 100
print(f"MAE  : {mae:.3f}")
print(f"MAPE : {mape:.2f}%")
# ── Feature importance ───────────────────────────────────
importance_df = pd.DataFrame({
    'feature'   : FEATURES,
    'importance': rf.feature_importances_
}).sort_values('importance', ascending=False)
print("\nFeature Importances:")
print(importance_df.to_string(index=False))
top_feature = importance_df.iloc[0]['feature']
print(f"\nMost important feature: {top_feature}")