Data Analytics — Full
Learning & Interview Notes
1. SQL
Core Concepts
Concept Notes
WHERE filters rows before
WHERE vs
aggregation; HAVING filters after
HAVING
GROUP BY
INNER vs INNER = only matching rows; LEFT
LEFT/RIGHT = all from left + matches; RIGHT =
JOIN all from right + matches
Table joined to itself — used for
Self-join hierarchies (employee-manager) or
row-to-row comparisons
CTE ( WITH ... AS ) is more
Subquery vs readable, reusable, supports
CTE recursion; subquery is fine for simple
one-off filters
Concept Notes
Speeds up reads
(WHERE/JOIN/ORDER BY) but
Index
slows down writes
(INSERT/UPDATE/DELETE)
Physically stores query results —
Materialized
faster reads, but data can go stale
view
until refreshed
Window Functions
ROW_NUMBER() → unique sequential (1,2,3,4)
RANK() → ties share rank, skips next (1,2,2,4)
DENSE_RANK() → ties share rank, no skip (1,2,2,3)
PARTITION BY vs GROUP BY → GROUP BY
collapses rows; window function with PARTITION
BY keeps all rows and adds aggregate alongside each
Key Query Patterns
2nd highest value (no LIMIT/OFFSET):
SELECT MAX(order_value) FROM orders
WHERE order_value < (SELECT
MAX(order_value) FROM orders);
Find duplicates:
SELECT customer_id, order_date, COUNT(*)
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;
Running total & MoM growth:
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS
running_total,
(revenue - LAG(revenue) OVER (ORDER BY
month)) / LAG(revenue) OVER (ORDER BY
month) AS mom_growth
FROM monthly_revenue;
Consecutive months (gaps & islands):
SELECT customer_id, order_month,
order_month - ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY
order_month) AS grp
FROM orders;
-- Same grp value = consecutive run
Query Optimization Checklist
1. Run EXPLAIN ANALYZE — check the execution
plan
2. Check indexes on JOIN/WHERE columns
3. Avoid SELECT *
4. Consider CTE/join instead of nested subquery
5. Consider partitioning for very large tables
2. Python / Pandas
Missing Data
Drop if missing < ~2-3% and random
Impute (mean/median) if missing at random and
volume matters
Add a "missing flag" column if missingness itself is
meaningful (e.g., no review score = never delivered)
Performance
Vectorization beats loops: pandas/numpy run
operations in optimized C across whole arrays
instead of row-by-row Python overhead
Use efficient dtypes ( category instead of object ,
downcast numeric types)
For data too big for memory:
pd.read_csv(chunksize=...) , or push heavy
lifting to SQL/Dask
Core Operations
Function Use
.loc Label-based selection
Integer position-based
.iloc
selection
SQL-style join on
merge()
columns/keys
join() Join on index by default
Stack dataframes without
concat()
key matching
Vectorized aggregation —
groupby().agg()
fast, use by default
Custom function per group
groupby().apply() — slower, use only when
agg can't express the logic
Merge validation (catch silent row explosions):
[Link](other, on='key',
validate='one_to_many')
Outlier detection:
IQR method: outlier if beyond Q1 - 1.5×IQR or Q3 +
1.5×IQR
Z-score: outlier if |z| > 3 (assumes roughly normal
data)
For skewed data (revenue, etc.): log-transform first or
use percentile capping
RFM Scoring Snippet
rfm['R_score'] = [Link](rfm['recency'], 5,
labels=[5,4,3,2,1]) # lower recency =
better = higher score
rfm['F_score'] =
[Link](rfm['frequency'].rank(method='first
'), 5, labels=[1,2,3,4,5])
rfm['M_score'] = [Link](rfm['monetary'],
5, labels=[1,2,3,4,5])
3. Excel
Topic Notes
VLOOKUP only looks right,
breaks on column insert.
VLOOKUP vs
INDEX-MATCH flexible,
INDEX-MATCH vs
doesn't break. XLOOKUP does
XLOOKUP
both + native error handling —
default choice if available
Topic Notes
Pivot tables/charts + slicers, fed
Dynamic by Excel Tables (auto-
dashboards expanding ranges), not
hardcoded ranges
COUNTIF/SUMIF for simple
COUNTIF/SUMIF conditions; SUMPRODUCT
vs SUMPRODUCT for complex multi-column
array logic
Use Power Query for
transformation; move to
Large datasets
SQL/Python if data exceeds
comfortable Excel scale
4. Tableau
Topic Notes
Live = real-time, slower, DB-
Live
dependent. Extract = snapshot,
connection vs
faster, can go stale — use extracts for
Extract
daily-refresh dashboards
Dimension vs Dimension = categorical, used to
Measure slice (region, category). Measure =
Topic Notes
quantitative, aggregated (revenue,
count)
Join = same granularity/source, row-
level combine. Blend = different
Join vs Blend granularities (daily sales vs monthly
target) — aggregates first, then
combines
Filter at data-source level, use
Performance extracts, limit marks rendered,
tuning minimize quick table calculations
and filter-triggered recalculations
Only include metrics that drive a
Dashboard
decision; cut vanity metrics; should
design
be interpretable in <5 seconds
5. Statistics
Concept Plain-English Explanation
Probability of seeing this result (or
more extreme) if there's truly no
P-value effect. Not "proof" — repeated testing
causes false positives (multiple
comparisons problem)
Concept Plain-English Explanation
"If we repeated this 100 times, the
Confidence true value would fall in this range ~95
interval times" — communicates uncertainty,
not a guarantee
Sample means trend toward normal
distribution as sample size grows,
Central Limit
regardless of underlying data shape —
Theorem
justifies using normal-based tests on
business data
False positive — concluding
Type I error something worked when it didn't
(wastes resources)
False negative — concluding
Type II error something didn't work when it did
(missed opportunity)
Correlation ≠ Always check for confounding
Causation variables before claiming a causal link
Check sample size, variance, and
Significance
whether change is outside historical
vs noise
variability — not just "did it move"
6. Machine Learning Basics
Concept Notes
Classification predicts a category
Classification vs (churn Y/N); Regression predicts a
Regression continuous number (predicted
revenue)
Model memorizes training
Overfitting data/noise — big gap between
train and test accuracy
Precision = of predicted positives,
how many correct. Recall = of
Precision vs actual positives, how many caught.
Recall For churn: often prioritize recall
— missing a real churner is costlier
than a false alarm
Why logistic Interpretability — stakeholders
regression over can see which factors drive an
complex models outcome and by how much
95% accuracy is meaningless if
classes are imbalanced (e.g., 5%
Accuracy trap
churn rate) — always check
confusion matrix / precision-recall
Elbow method or silhouette score
Validating for cluster count, but final check
clustering is: do clusters make business
sense?
7. Retail/E-Commerce Domain
Metrics
Metric Definition
AOV (Average
Revenue per order
Order Value)
CLV (Customer Total expected revenue from a
Lifetime Value) customer over the relationship
Churned customers ÷ total
customers at period start — define
Churn rate
"churned" using real purchase-gap
data, not a guess
Cart
(Carts created − completed orders)
abandonment
÷ carts created
rate
Recency, Frequency, Monetary —
RFM
customer scoring framework
Revenue trend, AOV, conversion
rate, repeat purchase rate, gross
Exec dashboard
margin, category performance —
KPIs
always pair reach metrics with
quality/conversion metrics
8. Judgment / Framework Answers
(for open-ended questions)
"Revenue dropped 15% — how do you investigate?"
1. Confirm it's real (rule out reporting/data error)
2. Segment the drop (all categories/regions, or
concentrated?)
3. Check seasonality / one-time events
4. Check funnel metrics (traffic → conversion → AOV)
to isolate where it happened
5. Form and validate a hypothesis before concluding
"How do you measure success of a new feature?"
1. Define the goal (engagement/conversion/retention)
2. Pick one primary metric tied to that goal
3. A/B test if possible
4. Define success criteria before seeing results
5. Check for negative side effects elsewhere
"95% accuracy — is that good?"
Depends on class balance — check confusion matrix, not
accuracy alone.
"One number to summarize company health?"
Push back gently — propose a small weighted scorecard
instead of one misleading number.
9. Full Project Structure
(Reference)
Retail Sales & Customer Analytics Platform (Olist
dataset)
1. Data Modeling (SQL): Star schema — fact_orders
+ dim_customers/products/sellers/geography;
cleaning, views
2. EDA (Python): Revenue trends, AOV, delivery-vs-
review correlation, payment mix
3. Segmentation (Python): RFM scoring + K-means
clustering, optional churn model (logistic regression,
precision/recall evaluated)
4. Dashboards (Tableau): Executive summary,
Customer segments, Operations
5. Business case: Translate findings into $ impact
recommendations
30-second interview summary:
"Built a full retail analytics pipeline on real e-commerce
data — modeled a star schema in PostgreSQL, ran EDA
and RFM/K-means segmentation in Python, and built
Tableau dashboards for execs, marketing, and ops. Key
finding was [your real number], translated into a business
recommendation with estimated revenue impact."
Next step: build the actual SQL schema + Python RFM
code so every number in these notes is real and
defensible in an interview.