This project contains a collection of advanced SQL analytics problems and optimized solutions built using MS SQL Server syntax. The dataset simulates a user login tracking system and demonstrates real-world SQL querying techniques frequently used in analytics engineering, business intelligence, and data analysis interviews.
The project focuses on solving practical business problems using SQL, including:
- User retention analysis
- Login activity tracking
- Quarterly business reporting
- Session trend analysis
- Gap analysis
- Ranking and window functions
- Recursive CTEs
- Time-series analysis
Contains user account details.
| Column Name | Description |
|---|---|
| user_id | Unique user identifier |
| user_name | User name |
| status | User status |
Contains login session activity.
| Column Name | Description |
|---|---|
| user_id | User identifier |
| login_timestamp | Login date and timestamp |
| session_id | Unique session identifier |
| session_score | Session engagement score |
- Common Table Expressions (CTEs)
- Recursive CTEs
- Window Functions
- RANK / LAG
- DATEADD / DATEDIFF
- Aggregate Functions
- EXISTS / NOT EXISTS
- Time-based filtering
- Quarter-based reporting
- Performance optimization techniques
- Users inactive in the last 5 months
- Quarterly user and session analysis
- Users active in January but inactive in November
- Quarter-over-quarter session growth analysis
- Highest session score by day
- Daily active user streak analysis
- Dates with no login activity
- MS SQL Server
- SQLite (converted query versions)
- SQL Window Functions
- Recursive Queries
├── dataset/
│ ├── create_tables.sql
│ ├── insert_data.sql
│
├── solutions/
│ ├── problem_solutions_mssql.sql
│ ├── problem_solutions_sqlite.sql
│
├── README.md- Create the database tables
- Execute the insert scripts
- Run the SQL solution files
- Compare MS SQL and SQLite implementations
This project is useful for:
- SQL interview preparation
- Data analyst practice
- Analytics engineering concepts
- Business reporting scenarios
- Query optimization practice
Vaibhav Kamal Nigam