🚀 Data Analyst Interview Questions with Answers — Part 7

🔍 Advanced Analytics & SQL Patterns

61. How do you compute month-on-month or week-on-week growth?

Growth compares current performance with a previous period.

📌 Formula:
Growth % = (Current Period - Previous Period) / Previous Period * 100

✅ Example SQL Query:
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS previous_month,
ROUND(
((revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month)) * 100,
2
) AS mom_growth
FROM sales;

This calculates month-on-month growth percentage.

62. How do you write a query to calculate retention or churn?

📌 Retention: Users who continue using the product
📌 Churn: Users who stop using the product

Example retention query:
SELECT signup_month,
COUNT(DISTINCT retained_user_id) * 100.0 /
COUNT(DISTINCT user_id) AS retention_rate
FROM retention_table
GROUP BY signup_month;

Retention analysis helps measure customer loyalty and product success.

63. How do you calculate LTV (Lifetime Value) conceptually?

LTV estimates the total revenue generated by a customer during their relationship with a business.

📌 Basic Formula:
LTV = Average Purchase Value Average Purchase Frequency Average Customer Lifespan

Businesses use LTV to evaluate customer acquisition and retention strategies.

64. How do you write a funnel analysis query?

Funnel analysis tracks user progression through stages.

Example funnel:
Signup → Activation → Purchase

Example SQL:
SELECT
COUNT(DISTINCT signup_user) AS signups,
COUNT(DISTINCT activated_user) AS activations,
COUNT(DISTINCT purchased_user) AS purchases
FROM funnel_data;

Funnels help identify where users drop off.

65. How do you handle time-based aggregations?

Time aggregations summarize data daily, weekly, or monthly.

Example:
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(revenue) AS total_revenue
FROM orders
GROUP BY month
ORDER BY month;

This helps track trends over time.

66. How do you compare cohorts?

Cohort analysis compares groups of users based on a shared characteristic.

Examples:
✔️ Users acquired in January vs February
✔️ Retention by signup month
✔️ Revenue by acquisition channel

Cohorts help measure long-term user behavior.

67. How do you calculate lead-time, cycle-time, or business-process metrics?

📌 Lead Time: Total time from request to completion
📌 Cycle Time: Time spent actively working on a task

Example Formula:
Lead Time = Completion Date - Request Date
Cycle Time = End Work Time - Start Work Time

These metrics help improve operational efficiency.

68. How do you implement A/B test-style analysis in SQL?

A/B testing compares two groups to measure performance differences.

Example:
SELECT test_group,
AVG(conversion_rate) AS avg_conversion
FROM experiment_results
GROUP BY test_group;

Analysts compare metrics such as:
✔️ Conversion rate
✔️ Revenue
✔️ Click-through rate
✔️ Retention

69. How do you approximate segmentation (RFM-style) in SQL?

RFM segmentation classifies customers using:

📌 Recency: How recently they purchased
📌 Frequency: How often they purchase
📌 Monetary: How much they spend

Example:
SELECT customer_id,
MAX(order_date) AS last_purchase,
COUNT(order_id) AS frequency,
SUM(amount) AS monetary
FROM orders
GROUP BY customer_id;

RFM helps identify high-value customers.

70. How do you document and version your SQL queries?

Best practices include:
✅ Use meaningful query names
✅ Add comments in SQL scripts
✅ Store queries in Git repositories
✅ Maintain version history
✅ Document assumptions and business logic
✅ Organize queries by project or folder structure
Proper documentation improves collaboration and maintainability.

🚀 Double Tap ❤️ For Part-8