Projects in SQL
Churn SQL
Problem
A fitness center was facing the challenge of silent student attrition. The lack of a system that integrated attendance history (turnstile) with financial status made it difficult to identify behavioral patterns—such as a sudden drop in workouts and late monthly payments—that precede the definitive cancellation of membership.
Solution
Development of a relational data architecture and analytical queries to transform raw data into an early retention strategy. The project involved:
-
Relational Modeling (DDL/DML): Creation and organization of Student and Attendance tables linked by foreign keys;
-
Cross-referencing information: Using complex JOINS to link training behavior to each client's financial status;
-
Conditional Logic: Applying CASE WHEN to automate alerts and engagement classification.
Result
The project resulted in a monitoring system that categorizes students by risk levels:
-
Critical Risk (Financial): Students with overdue tuition payments and low attendance;
-
Attention (Low Engagement): Students with payments up to date, but who attended the facility less than 5 times in the last month;
-
Active Loyalty: Generating automatic lists for the marketing team to act proactively on "Attention" profiles, increasing the chances of retention;
-
Automation: Structuring weekly queries to ensure the sales priority list is always up-to-date.
Tool used
-
SQL (Structured Query Language): Central language for data manipulation and extraction;
-
DB-Fiddle: Environment used for simulating the database architecture and executing queries.
Data source
-
Fictitious Relational Database: Containing access history (turnstile), student profiles, and records of monthly and quarterly payments.
Technical documentation
[Access the Complete SQL Code in Action (DB-Fiddle)]:
Sales SQL
Problem
A bookstore specializing in classic Brazilian literature faced a lack of business intelligence to manage its catalog. Without efficient data cross-referencing, it was difficult to identify which works and authors generated the highest revenue and which had the highest inventory turnover, resulting in management based on assumptions rather than real revenue data.
Solution
Structuring a relational database to ensure data integrity and allow for in-depth analysis. The project involved:
Relational Data Launch (DDL): Creation of "Books" and "Sales" tables using primary and foreign keys to ensure data consistency;
Data Manipulation and Integration (DML/Joins): Population of records with classic works and use of JOINS to connect the collection to the history of operational transactions;
Ranking Analysis (Aggregations): Use of aggregation and sorting functions (ORDER BY) for total revenue calculations and trend identification.
Result
Extracting vital KPIs (Key Performance Indicators) allowed for more assertive decisions:
-
Revenue Ranking: Identification of works such as "Grande Sertão: Veredas" and "O Auto da Compadecida" as absolute revenue leaders;
-
Demand Mapping: Visualization of the most popular authors, allowing for prioritization of the exposure and marketing of works at the top of the ranking;
-
Strategic Actions: Recommendation of promotional bundles for works with lower turnover and adjustment of stock levels for high-turnover items, avoiding stockouts.
Tool used
-
SQL (Structured Query Language): Central technology for architecture and analysis;
-
DB-Fiddle: A simulation environment for executing queries in real time.
Data source
-
A database comprised of a detailed literary collection (titles, authors, and unit prices) cross-referenced with the bookstore's operational transaction history.
Technical documentation
[Access the Complete SQL Code in Action (DB-Fiddle)]: