🎬 Maven Movies – Business Analytics Case Study (SQL)

A structured acquisition-style SQL case study evaluating financial stability, inventory risk, and customer monetization in a two-store retail business.

📄 View Full Executive Summary

---

🚀 Project Impact Snapshot

• Analyzed $67K+ revenue across a two-store retail model
• Evaluated $92K+ inventory exposure and capital efficiency
• Identified 44 slow-moving high-cost titles impacting ROI
• Assessed customer lifetime value and revenue concentration
• Built 16 structured SQL analyses using relational joins, CTEs, and window functions

---

📌 Business Context

A potential acquirer requested a full operational and financial review of Maven Movies, a two-location DVD rental business.

The objective was to determine:

• Financial stability
• Revenue drivers
• Inventory risk exposure
• Customer value distribution
• Operational consistency

All analysis was conducted using MySQL on a 16-table relational (Sakila-style) schema.

---

📊 Key Business Metrics

Metric	Value
Total Annual Revenue	~$67,406
Total Inventory Units	4,583
Total Replacement Cost	~$92,621
Active Customers	584
Top 10% Revenue Share	14.32%
Slow-Moving Titles Identified	44
Peak Monthly Revenue	~$28,374 (July)

---

📈 Visual Insights

1️⃣ Monthly Revenue Trend

Seasonality analysis shows strong mid-year demand spikes.

---

2️⃣ Revenue Comparison by Store

Both stores generate nearly identical revenue, demonstrating operational consistency across locations.

---

3️⃣ Revenue by Film Category

Revenue is diversified across 16 categories, with Sports, Sci-Fi, and Animation leading.

---

4️⃣ Top Customers by Lifetime Value (CLTV)

Top customers generate significantly higher lifetime value, highlighting opportunity for targeted retention strategies.

---

5️⃣ High Replacement Cost – Low Rental Titles

Identifies capital tied up in slow-moving inventory requiring performance review or clearance strategy.

---

🔍 Core Analyses Performed

1️⃣ Revenue & Store Performance

• Compared revenue across both store locations
• Analyzed monthly revenue trends and seasonality
• Identified peak demand periods

2️⃣ Inventory & Capital Risk

• Measured replacement cost exposure by store and category
• Identified slow-moving, high-cost inventory
• Evaluated category-level capital allocation

3️⃣ Film & Category Profitability

• Ranked top revenue-generating films
• Analyzed revenue by film category
• Assessed inventory diversification and demand alignment

4️⃣ Customer Analytics

• Calculated Customer Lifetime Value (CLTV)
• Measured revenue concentration by decile
• Evaluated repeat rental behavior
• Compared active customers across stores

5️⃣ Governance & Structure

• Assessed operational readiness for acquisition
• Structured findings for executive-level reporting

Each analysis required multi-table joins across:

• customer
• rental
• payment
• inventory
• film
• film_category
• store
• staff

---

🧠 Example SQL (Customer Lifetime Value)

SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    COUNT(r.rental_id) AS total_rentals,
    SUM(p.amount) AS total_payments
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY c.customer_id
ORDER BY total_payments DESC;

This query powers CLTV ranking and revenue concentration analysis.

---

🎯 Strategic Insights

• Revenue is operationally balanced across stores
• Inventory diversification reduces category-level risk
• Moderate revenue concentration suggests retention growth opportunity
• Clear seasonality indicates need for demand-aligned inventory planning
• Capital inefficiency exists within slow-moving high-cost titles

---

🚀 Strategic Recommendations

• Implement structured inventory performance reviews for underperforming titles.
• Align marketing campaigns with peak seasonal demand.
• Introduce a tiered loyalty program targeting high-value customers.
• Monitor category-level ROI to optimize capital allocation.
• Deploy recurring KPI dashboards for performance governance

---

🛠 Technical Skills Demonstrated

SQL & Data Engineering

• Complex multi-table joins
• Common Table Expressions (CTEs)
• Window functions (RANK, NTILE)
• Aggregations & grouping
• Revenue segmentation logic

Business Analytics

• Customer lifetime value modeling
• Revenue concentration analysis
• Inventory capital risk evaluation
• Time-series seasonality analysis
• Executive-level reporting structure

---

📂 Project Structure

Maven-Movies-Project/
│
├── sql/                  # Database schema + analysis queries
├── data/                 # Exported results of 16 analyses
├── images/               # Visualizations used in README
├── presentation/         # Slide deck (PDF + PPT)
├── executive_summary.md
└── README.md

---

🔁 How to Reproduce

1. Install MySQL or MariaDB.
2. Load the schema:

SOURCE sql/create_maven_movies_2022-04.sql;

3. Run the analysis scripts from the sql/ folder.
4. Export query outputs to CSV if needed.

---

🎯 Why This Project Matters

This project demonstrates my ability to:

• Translate business questions into structured SQL analysis
• Connect operational metrics to measurable financial impact
• Identify risk exposure and optimization opportunities
• Apply CTEs, window functions, and multi-table joins in real scenarios
• Communicate technical findings clearly to non-technical stakeholders

---

📊 Presentation

Slides and a printable PDF are available in the presentation/ folder:

• Project Presentation (PPTX)
• Project Presentation (PDF)

Presenter: Manohar K
Date: 27-08-2026

Highlights:

• High-level findings and business recommendations
• How to reproduce the demo and view key charts

---

📬 Contact & Professional Profile

If you would like to discuss this project or explore collaboration opportunities:

---

💼 Open to Opportunities

Currently open to full-time roles in:

• Data Analyst
• Business Intelligence Analyst
• SQL Developer
• Reporting / Analytics Specialist

---

⭐ If you found this project insightful, feel free to star the repository or reach out - I’m always open to feedback and collaboration.