Basics of Data Analytics You Must Know

-- Descriptive + diagnostic — SQL.
-- The two types of analytics you will do most often.

-- DESCRIPTIVE: what happened last month?
SELECT
  region,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND order_date <  DATE_TRUNC('month', CURRENT_DATE)
GROUP BY region
ORDER BY revenue DESC;

-- Sample output:
-- region    orders   revenue
-- North     4,200    1,240,000
-- West      3,800    1,100,000
-- South     3,200      980,000
-- East      2,400      880,000

-- DIAGNOSTIC: why did North drop vs last month?
WITH this_month AS (
  SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
  FROM orders
  WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
    AND order_date <  DATE_TRUNC('month', CURRENT_DATE)
  GROUP BY region
),
last_month AS (
  SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
  FROM orders
  WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 month')
    AND order_date <  DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  GROUP BY region
)
SELECT
  t.region,
  l.orders AS last_orders, t.orders AS this_orders,
  t.orders - l.orders AS order_change,
  l.revenue AS last_revenue, t.revenue AS this_revenue,
  ROUND((t.revenue - l.revenue) * 100.0 / NULLIF(l.revenue, 0), 1) AS revenue_pct_change
FROM this_month t
JOIN last_month l USING (region)
ORDER BY revenue_pct_change;

-- What this teaches:
-- - Descriptive answers "what happened"
-- - Diagnostic answers "why did it happen"
-- - Both are pure SQL — no Python needed
--
-- What interviewers ask:
-- "How would you figure out why revenue dropped?"
-- The answer is a descriptive query, then a diagnostic one.
-- This is the core of entry-level analytics work.

# Excel — pivot table analysis.
# The fastest way to explore data before writing SQL.

# ── Step 1: Load the data ──
# Open the CSV in Excel. Data → From Text/CSV
# Or copy-paste into a sheet.
#
# Columns: order_id, order_date, region, category, amount, quantity

# ── Step 2: Insert a pivot table ──
# Select the data → Insert → PivotTable → New Worksheet
#
# Rows:    region
# Columns: category
# Values:  SUM(amount)
#
# This produces a matrix of revenue by region and category in seconds.

# ── Step 3: Add a calculated field ──
# PivotTable Analyze → Fields, Items, Sets → Calculated Field
# Name: avg_order
# Formula: = amount / quantity
#
# This adds a new measure to the pivot without changing the source.

# ── Step 4: Ask a diagnostic question ──
# "Which region-category combination dropped most vs last quarter?"
#
# Add a second pivot table with last quarter's data.
# Compare the two side by side. Or use a formula:
#   =IFERROR((this_qtr - last_qtr) / last_qtr, "")
#
# ── What Excel does well ──
# - Fast (open, click, done)
# - Universal (every stakeholder has it)
# - Perfect for exploration and one-off analysis
#
# ── What Excel struggles with ──
# - Large data (>1M rows gets slow)
# - Repeatable pipelines (manual steps, not auditable)
# - Sharing at scale (files get copied, versions diverge)
#
# The pattern:
# 1. Use Excel to explore and form hypotheses
# 2. Use SQL to confirm at scale
# 3. Use a BI tool to deliver

# Excel is the analyst's workbench. Not the delivery system.

# The full ladder — from "what" to "what next".
# Every analytics project climbs this ladder.

# ── Descriptive: what happened? ──
# "Revenue was ₹4.2 crore in September."
# Tools: SQL, Excel, pivot tables.
# Skill level: beginner.
# Time: minutes to hours.

# ── Diagnostic: why did it happen? ──
# "Revenue dropped 12% because the North region lost
#  300 orders, and those orders were concentrated in
#  the electronics category."
# Tools: SQL with joins, Excel with formulas, some stats.
# Skill level: intermediate.
# Time: hours to days.

# ── Predictive: what will happen? ──
# "North region revenue will recover 8% next month
#  based on the last 3 months of trend data."
# Tools: Python (pandas, scikit-learn), time-series models.
# Skill level: intermediate to advanced.
# Time: days to weeks.

# ── Prescriptive: what should we do? ──
# "Run a 10% discount campaign in North India for
#  electronics for 2 weeks — expected revenue recovery
#  is ₹1.2 crore at a cost of ₹40 lakh."
# Tools: optimisation, simulation, or just good judgment.
# Skill level: senior analyst / data scientist.
# Time: weeks.

# ── The climb ──
#
# 80% of entry-level analyst work is descriptive.
# 15% is diagnostic.
# 5% is predictive or prescriptive.
#
# Why this matters for beginners:
# - Do not panic about machine learning — you do not need it
#   for most analyst roles.
# - Master descriptive and diagnostic first.
# - Predictive and prescriptive come later, usually in
#   senior or specialised roles.

# The interview question:
# "Walk me through a project you did end to end."
# The answer should cover at least descriptive and diagnostic,
# and reflect on what would have made it predictive.