Projects in SQL
SQL Churn Analysis
Context
This project aims to solve the challenge of student attrition in a fitness center. The objective was to use SQL to identify behavioral patterns—such as decreased attendance and payment delays—that precede enrollment cancellations, transforming raw data into an early retention strategy.
Data source
A relational database containing access history (turnstile), student profiles, and records of monthly and quarterly payments for the unit.
Data preparation
The structuring was carried out through the creation of a relational database (DDL) and record manipulation (DML), focusing on:
Relational Modeling: Organization of Student and Attendance tables linked by foreign keys.
Information Cross-referencing: Use of complex joins to combine training behavior with the financial status of each client.
Tool used
Technology: SQL (Structured Query Language).
Technical Resources: Application of Conditional Logic (CASE WHEN) for automating alerts and analytical queries to filter low-engagement data.
Conclusion
The project resulted in the creation of a predictive monitoring system that classifies students into risk categories:
1. Critical Risk (Financial): Students with overdue tuition payments and low attendance.
2. Attention (Low Engagement): Students with payments up to date, but who attended the unit less than 5 times in the last month.
3. Priority Identification: Generation of automatic lists so that the marketing team can focus its efforts on clients with the highest probability of cancellation.
Recommended actions
1. Active Loyalty: Implement immediate contact actions for students in the "Attention" category, seeking to understand the reason for the drop in attendance.
2. Financial Recovery: Create special conditions or friendly reminders for "Critical Risk" profiles before their plan expires.
3. Alert Automation: Configure the query to run weekly, ensuring that the sales team's priority list is always up-to-date.
Technical documentation
[Access the Complete SQL Code in Action (DB-Fiddle)]:
SQL Sales Analysis
Context
This project was developed to address the lack of business intelligence in a bookstore focused on classic Brazilian literature. The main objective was to identify which works and authors generate the highest revenue and which have the highest inventory turnover, allowing for catalog management based on real revenue data.
Data source
The database consists of a detailed literary collection (titles, authors, and unit prices) cross-referenced with the bookstore's operational transaction history.
Data preparation
The structuring was carried out using relational modeling to ensure data integrity, including:
Table Creation (DDL): Structuring the "Books" and "Sales" tables with primary and foreign keys.
Data Population (DML): Insertion of records of classic works and actual sales data for analysis.
Tool used
Technology: SQL (Structured Query Language).
Technical Resources: Application of Joins for connecting inventory and sales, Aggregations for revenue calculations, and Sorting functions (ORDER BY) for generating rankings.
Conclusion
Through analytical queries, it was possible to extract vital KPIs (Key Performance Indicators) for the business:
Revenue Ranking: Identification that works such as "Grande Sertão: Veredas" and "O Auto da Compadecida" lead in total revenue.
Sales Volume: Mapping of demand by author, allowing visualization of the most popular authors in the catalog.
Recommended actions
1. Catalog Highlights: Prioritize the display and marketing of works identified at the top of the revenue ranking to maximize income.
2. Inventory Management: Maintain higher inventory levels for high-turnover authors, avoiding stockouts of the most sought-after items.
3. Strategic Promotions: Create combos or promotions for lower-turnover works based on total revenue data to balance inventory.
Recommended actions
[Access the Complete SQL Code in Action (DB-Fiddle)]: