EasyFlix Management System - Part 1 Research & Design

# EasyFlix Management System - Part 1 Research & Design

---

## INVESTIGATE

### Timeline and Schedule

| **Phase** | **Task** | **Duration** | **Completion %** |
|-----------|----------|--------------|------------------|
| **Phase 1** | **Research & Design** | 3 days | 100% |
| | Problem analysis | 1 day | |
| | Database design & ERD | 1 day | |
| | Interface mockups | 1 day | |
| **Phase 2** | **Development** | 5 days | 100% |
| | Database implementation | 1 day | |
| | Core functions | 1 day | |
| | User interface | 1 day | |
| | Admin interface | 1 day | |
| | Extra buffer time for unexpected delays | 1 day | |
| **Phase 3** | **Testing & Documentation** | 2 days | 100% |
| | System testing | 1 day | |
| | Final documentation | 1 day | |

Phase 1 - Research and design appropriate solution to the problem. Ensure proper planning and infrastructure to build on is created.

Phase 2 - Begin developement, noting down changes and relevant information during the process. Create programs and database.

Phase 3 - Test system and create final documentation on project and how well it solved the issue.

### Problem Outline

EasyFlix is an online media service, they need a software solution for customers so they can create an account, purchase a subscription, rent shows, e.t.c. They also need a solution to track financial information and record sales. This should be done using a software solution that interacts with an SQL database, reading, storing and removing values.

Note: During developement some ideas and names have changed, such as rent --> buy

### Problem Description

EasyFlix has many movies and tv shows available to watch. It offers two subscription levels, basic and premium. Basic users will have access to all movies that are in the basic access group. EasyFlix offers many shows in the basic subscription but some shows may be marked as premium. These premium shows can be rented for an additional cost. Users who purchase the premium subscription will gain access to all basic and premium shows without needing to rent.

The owners need a software solution that allows users to access the EasyFlix catalogue with a subscription and purchase shows. They need to be able to sign up and log in, and interact with the available shows and more.

They also need an admin software solution for managing the users, tracking sales, statistics, finances, adding/removing available shows and changing show access groups.

This all needs to be done with an SQL database to store the values, ensuring optimal and consistent security, integrity and availability of the data.

#### Planned Solutions:

EasyFlixUser is a python-based application designed for users, giving them the ability to do the following;
- Login or Create an account
- Choose or manage subscription level
- Manage their account (Password changes, account termination)
- Browse show catalogue
- Add shows to account
- Rent or return premium shows (if on basic plan)

EasyFlixAdmin is another Python-based application designed for an administrator of EasyFlix to access all aspects of it's media service including;
- User account management (Account creation, password changes, account termination)
- Subscription management (Price changes)
- Media content managment (add movies/tv shows, modify show access group, modify rent price)
- Access general statistics
- Access financial data
- See current rentals
- See past rentals

#### Detailed Requirements

**Programming Requirements:**
- Python 3.x application with SQLite3 database integration
- Clean, professional UI (will use Textual GUI)
- Modular code structure for better maintainability

**Database Requirements:**
- ~Dual database architecture (Separated sensitive data from non-sensitive)~
- Complete data integrity, ensuring data is accurate, complete and consistent
- Database backup ability, ensuring data safety
- Easily available non-sensitive data, authentication required for sensitive data
- Data security all passwords will be stored as a hash using SHA-256 with salt

**Functional Requirements**
- Users able to manage accounts
     - Create account
     - Delete account
     - Change password
     - Change subscription
- Users able to add, rent and return shows
- Users able to access show catalogue
- Admins able to manage catalogue
     - Change show access group
     - Change show rent cost
- Admins able to see rentals, financial statistics, users, overall information
- Data needs to be easily input-able one way or another (will be done automatically through GUI, hence user application)
- Information readily available to authorised parties
- Information readily editable to authorised parties

#### Ethical, Legal and Security Issues
Requirements and things to keep in mind regarding ethics, laws and security

**Security Issues:**
- Password storage must use SHA-256 hashing with unique salts
     - Hashes must be used to protect the passwords in the database in the rare case of a data breach
     - This ensures passwords aren't just released to the internet in a worst case scenario
     - Salts are used to make the cracking process much harder
- Input validation to prevent SQL injection attacks
     - Use proper sanitisation to prevent sql injection and data breaches
     - The API middleman also helps this and dramatically increases security by adding another step
- Administrative access control and session management
     - Proper login details for admins, will also have a stored hash
- All access to sensitive data requires admin authentication
- Secure communication between programs and API
     - The programs may still be sending sensitive data on it's way to the database. Encrypting the communication between the programs and API will put a shield around that information and an especially strong encryption will be a very protective guard against malicious attacks

**Legal Issues:**
- Compliance with data protection regulations for customer data & No storage of advertising data for opted out users
     - Any merketing data should only be collected with consent
     - If consent is withdrawn the information should be removed
- Secure storage of personal information (names, emails)
     - Ties in with security, look after sensitive data properly
- Administrative responsibility for user data management
     - Use of admin controls should be strictly professional and it would even be a good idea to implement guidelines

**Ethical Issues:**
- Responsible handling of customer personal information
     - Ties in with above and security, proper storage and protection for sensitive data
- Transparent subscription and rental cost management
     - All costs are easily readable and presented for easy understanding
- Fair content access based on subscription levels
     - Reasonable show choices and 'moneys-worth' for subscription levels
- Ethical data collection and usage practices
     - User is opted out by default, respect to the user
- Opt in for data sharing
     - As above

#### Data Quality Factors

**Data Accuracy:**
- Input validation to ensure data meets specific criteria
- Format checking (e.g., email addresses, phone numbers)
- Range validation for numerical data
- Required field validation

**Data Consistency:**
- Referential integrity between customers and rentals
- Synchronized statistics and financial totals
- Consistent naming conventions and data formats
- Real-time updates across all related tables

**Data Security:**
- Encrypted password storage for all users
- Database file protection and access control
- Administrative audit trails and logging
- User authentication and authorization

**Data Types and Constraints**
- Proper data type selection (INTEGER, VARCHAR, DATE, etc.)
- Primary key constraints to ensure uniqueness
- Foreign key constraints to maintain referential integrity
- Check constraints to enforce business rules

**Data Entry Controls**
- User interface design that prevents incorrect data entry
- Drop-down menus and selection lists to limit input options
- Data entry training for users

**Database Design**
- Normalization to reduce data redundancy
- Proper table relationships
- Well-defined entity relationships
- Appropriate indexing strategies

**Backup and Recovery**
- Regular automated backups
     - Would be helpful to implement, will add to future features
- Recovery procedures and testing
- Transaction logs
- Disaster recovery planning

**Human Factors**
- User training and education
- Clear data entry procedures
- Error reporting mechanisms
- Quality assurance processes

---

## DESIGN

### OLD DESIGN (THIS WAS REVAMPED IN DEVELOPEMENT)

### Entity Relation Diagram

<img width="1143" height="1952" alt="image" src="https://github.com/user-attachments/assets/b4c1eeb5-f353-4c68-b9ce-578330a4f8c4" />

### Relational Notation

CUSTOMERS(User_ID, Username, Email, Password_Hash, Salt, Subscription_Level, Shows, Rentals, Total_Spent, Opted_In, Favourite_Genre)
Primary Key: User_ID

SHOWS(Show_ID, Name, Release_Date, Rating, Director, Length, Genre, Access_Group, Cost_To_Rent)
Primary Key: Show_ID

RENTALS(Rental_ID, User_ID, Show_ID, Rental_Date, Return_Date, Expired, Cost)
Primary Key: Rental_ID
Foreign Keys: User_ID references CUSTOMERS(User_ID)
             Show_ID references SHOWS(Show_ID)

STATISTICS(Date, Total_Shows_Rented, Total_Subscriptions, Total_Users, Last_Updated)
Primary Key: Date

FINANCIALS(Date, Total_Revenue_Rent, Total_Revenue_Subscriptions, Total_Combined_Revenue, Last_Updated)
Primary Key: Date

### Data Dictionary

#### CUSTOMERS
Stores user account information including authentication details, subscription level, and user preferences. This table contains sensitive customer data requiring secure access.

#### SHOWS
Contains the complete media catalog with show details, ratings, and access control information. This determines what content is available to different subscription tiers.

#### RENTALS
Records all rental transactions, tracking which users have rented which shows, rental periods, and associated costs. Links customers to shows through rental relationships.

#### STATISTICS
Maintains system-wide statistics for administrative reporting and business intelligence. Updated regularly to provide current system metrics.

#### FINANCIALS
Tracks revenue and financial data across all income streams. Critical for business reporting and financial analysis.

### Detailed Field Descriptions

#### CUSTOMERS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| User_ID | INTEGER | - | PRIMARY KEY, NOT NULL | Unique identifier for each customer |
| Username | VARCHAR | 50 | UNIQUE, NOT NULL | User's chosen display name |
| Email | VARCHAR | 100 | UNIQUE, NOT NULL | User's email address for account |
| Password_Hash | VARCHAR | 64 | NOT NULL | SHA-256 hashed password |
| Salt | VARCHAR | 32 | NOT NULL | Unique salt for password hashing |
| Subscription_Level | VARCHAR | 20 | NOT NULL, CHECK IN ('Basic', 'Premium') | User's current subscription tier |
| Shows | TEXT | - | - | List of shows added to user's account |
| Rentals | TEXT | - | - | History of user's rental activity |
| Total_Spent | DECIMAL | 10,2 | DEFAULT 0.00 | Total amount spent by user |
| Opted_In | BOOLEAN | - | DEFAULT FALSE | Indicates whether user has opted in to share data for marketing |
| Favourite_Genre | VARCHAR | 30 | - | User's preferred content genre, NULL unless Opted_In is TRUE |

#### SHOWS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Show_ID | INTEGER | - | PRIMARY KEY, NOT NULL | Unique identifier for each show |
| Name | VARCHAR | 100 | NOT NULL | Title of the show or movie |
| Release_Date | DATE | - | NOT NULL | Original release date |
| Rating | VARCHAR | 10 | NOT NULL | Content rating (G, PG, M, MA15+, R18+) |
| Director | VARCHAR | 100 | NOT NULL | Director's name |
| Length | INTEGER | - | NOT NULL | Duration in minutes |
| Genre | VARCHAR | 30 | NOT NULL | Primary genre classification |
| Access_Group | VARCHAR | 20 | NOT NULL, CHECK IN ('Basic', 'Premium') | Subscription level required |
| Cost_To_Rent | DECIMAL | 5,2 | NOT NULL | Rental price for basic subscribers |

#### RENTALS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Rental_ID | INTEGER | - | PRIMARY KEY, NOT NULL | Unique identifier for each rental |
| User_ID | INTEGER | - | FOREIGN KEY, NOT NULL | References CUSTOMERS(User_ID) |
| Show_ID | INTEGER | - | FOREIGN KEY, NOT NULL | References SHOWS(Show_ID) |
| Rental_Date | DATE | - | NOT NULL | Date rental was initiated |
| Return_Date | DATE | - | - | Date rental was returned (NULL if active) |
| Expired | BOOLEAN | - | DEFAULT FALSE | Whether rental period has expired |
| Cost | DECIMAL | 5,2 | NOT NULL | Amount charged for this rental |

#### STATISTICS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Date | DATE | - | PRIMARY KEY, NOT NULL | Date for which statistics are recorded |
| Total_Shows_Rented | INTEGER | - | DEFAULT 0 | Count of all rental transactions for this date |
| Total_Subscriptions | INTEGER | - | DEFAULT 0 | Count of active subscriptions on this date |
| Total_Users | INTEGER | - | DEFAULT 0 | Count of registered users on this date |
| Last_Updated | DATETIME | - | NOT NULL | Timestamp of last statistics update |

#### FINANCIALS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Date | DATE | - | PRIMARY KEY, NOT NULL | Date for which financial data is recorded |
| Total_Revenue_Rent | DECIMAL | 10,2 | DEFAULT 0.00 | Total revenue from rentals for this date |
| Total_Revenue_Subscriptions | DECIMAL | 10,2 | DEFAULT 0.00 | Total revenue from subscriptions for this date |
| Total_Combined_Revenue | DECIMAL | 10,2 | DEFAULT 0.00 | Sum of all revenue streams for this date |
| Last_Updated | DATETIME | - | NOT NULL | Timestamp of last financial update |

### Database Queries

#### User Management Queries

1. **Find all premium subscribers**
   - Query the CUSTOMERS table to retrieve all users who have a subscription level of 'Premium'
   - This helps administrators understand their premium customer base

2. **Get customer spending summary**
   - Query the CUSTOMERS table to find users who have spent more than a specified amount
   - Useful for identifying high-value customers and creating loyalty programs

3. **List customers by favorite genre**
   - Query the CUSTOMERS table to group users by their favorite genre
   - Helps with content recommendation and targeted marketing
   - Applies only to customers who opted in to marketing, else those values will be NULL

#### Content Management Queries

4. **Find all shows available to basic subscribers**
   - Query the SHOWS table to retrieve shows where the access group is 'Basic'
   - Used by the user application to display available content for basic users

5. **Get most expensive shows to rent**
   - Query the SHOWS table to find shows with the highest rental costs
   - Helps administrators analyze pricing strategies

6. **List shows by genre and rating**
   - Query the SHOWS table to filter content by both genre and content rating
   - Useful for content curation and parental controls

#### Rental Analysis Queries

7. **Find currently active rentals**
   - Query the RENTALS table to find all rentals where the return date is NULL
   - Shows which rentals are currently active in the system

8. **Calculate total rental revenue for a specific period**
   - Query the RENTALS table to sum up all rental costs within a date range
   - Essential for financial reporting and business analysis

9. **Find customers with overdue rentals**
   - Query the RENTALS table to find rentals where the expired flag is TRUE and return date is NULL
   - Helps with rental management and customer follow-up

#### Complex Relationship Queries

10. **Get customer rental history with show details**
    - Join CUSTOMERS, RENTALS, and SHOWS tables to show complete rental history
    - Provides comprehensive view of customer activity with show information

11. **Find most popular shows by rental count**
    - Join RENTALS and SHOWS tables, group by show and count rentals
    - Identifies trending content and popular titles

12. **Calculate average spending per customer by subscription type**
    - Join CUSTOMERS with RENTALS, group by subscription level and calculate averages
    - Analyzes spending patterns across different subscription tiers

#### Administrative Reporting Queries

13. **Generate monthly revenue report**
    - Query FINANCIALS table to track revenue trends over time
    - Essential for business performance monitoring

14. **Get system usage statistics**
    - Query STATISTICS table to retrieve current system metrics
    - Provides overview of platform growth and usage

15. **Find customers who haven't rented any shows**
    - Use LEFT JOIN between CUSTOMERS and RENTALS to find users with no rental history
    - Identifies users who might need engagement strategies
    - Make sure to only check Basic users
   
16. **Find inactive accounts**
    - Query the CUSTOMERS table to find users with no show history
    - Identifies users who are inactive

#### Security and Maintenance Queries

17. **Verify data integrity between tables**
    - Check that all foreign key relationships are maintained properly
    - Ensures database consistency and prevents orphaned records

#### Time-Based Analysis Queries

18. **Get financial data for a specific date**
   - Query the FINANCIALS table using the Date primary key to retrieve revenue information for a particular day
   - Essential for daily financial reporting and tracking

19. **Find statistics trends over a date range**
   - Query the STATISTICS table to compare user growth and rental activity across multiple dates
   - Useful for identifying business trends and growth patterns

20. **Calculate monthly revenue totals**
   - Query the FINANCIALS table to sum revenue across all days in a specific month
   - Provides monthly financial summaries for business reporting

21. **Track daily user growth**
   - Query the STATISTICS table to show how Total_Users changes day by day
   - Monitors platform growth and user acquisition rates

22. **Find peak rental days**
   - Query the STATISTICS table to identify dates with the highest Total_Shows_Rented
   - Helps identify popular viewing periods and plan capacity

### NEW DESIGN

### Entity Relation Diagram

<img width="1304" height="2457" alt="image" src="https://github.com/user-attachments/assets/efb0f986-ac64-4b12-af5b-3544686433b9" />

## Relational Notation

**ADMIN_CREDENTIALS**(Admin_ID, Username, Password_Hash, Salt, Role, Created_Date)
- Primary Key: Admin_ID

**CUSTOMERS**(User_ID, Username, Email, Password_Hash, Salt, Subscription_Level, Shows, Total_Spent, Favourite_Genre, Marketing_Opt_In)
- Primary Key: User_ID

**SHOWS**(Show_ID, Name, Release_Date, Rating, Director, Length, Genre, Access_Group, Cost_To_Buy)
- Primary Key: Show_ID

**BUYS**(Buy_ID, User_ID, Show_ID, Buy_Date, Cost)
- Primary Key: Buy_ID
- Foreign Keys: User_ID references CUSTOMERS(User_ID), Show_ID references SHOWS(Show_ID)

**STATISTICS**(Date, Total_Shows_Bought, Total_Subscriptions, Premium_Subscriptions, Basic_Subscriptions, Total_Users, Last_Updated)
- Primary Key: Date

**FINANCIALS**(Date, Total_Revenue_Buys, Total_Revenue_Subscriptions, Premium_Subscription_Revenue, Basic_Subscription_Revenue, Total_Combined_Revenue, Last_Updated)
- Primary Key: Date

## Data Dictionary

### ADMIN_CREDENTIALS
Stores administrator account information with secure authentication credentials for admin portal access.

### CUSTOMERS
Contains user account information including authentication details, subscription level, and user preferences. The Shows field stores comma-separated show IDs representing the user's collection.

### SHOWS
Complete media catalog with show details, ratings, and access control information. Access_Group determines subscription level required, Cost_To_Buy applies when basic users purchase premium content.

### BUYS
Records purchase transactions when basic subscribers buy premium shows. Creates transactional history linking customers to purchased shows.

### STATISTICS
Daily system statistics for administrative reporting and business intelligence. Automatically updated with current system metrics.

### FINANCIALS
Daily financial data tracking revenue from subscriptions and purchases. Separates revenue streams for detailed business analysis.

## Detailed Field Descriptions

### ADMIN_CREDENTIALS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Admin_ID | INTEGER | - | PRIMARY KEY, AUTOINCREMENT | Unique administrator identifier |
| Username | VARCHAR | 50 | UNIQUE, NOT NULL | Admin login username |
| Password_Hash | VARCHAR | 64 | NOT NULL | SHA-256 hashed password |
| Salt | VARCHAR | 32 | NOT NULL | Unique salt for password hashing |
| Role | VARCHAR | 20 | DEFAULT 'admin' | Administrator role designation |
| Created_Date | DATETIME | - | DEFAULT CURRENT_TIMESTAMP | Account creation timestamp |

### CUSTOMERS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| User_ID | INTEGER | - | PRIMARY KEY, AUTOINCREMENT | Unique customer identifier |
| Username | VARCHAR | 50 | UNIQUE, NOT NULL | User's chosen username |
| Email | VARCHAR | 100 | UNIQUE, NOT NULL | User's email address |
| Password_Hash | VARCHAR | 64 | NOT NULL | SHA-256 hashed password |
| Salt | VARCHAR | 32 | NOT NULL | Unique salt for password hashing |
| Subscription_Level | VARCHAR | 20 | NOT NULL, CHECK IN ('Basic', 'Premium') | Current subscription tier |
| Shows | TEXT | - | - | Comma-separated list of show IDs in collection |
| Total_Spent | DECIMAL | 10,2 | DEFAULT 0.00 | Total amount spent including subscriptions |
| Favourite_Genre | VARCHAR | 30 | - | Auto-calculated preferred genre from viewing history |
| Marketing_Opt_In | BOOLEAN | - | DEFAULT 0 | Marketing communication preference |

### SHOWS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Show_ID | INTEGER | - | PRIMARY KEY, AUTOINCREMENT | Unique show identifier |
| Name | VARCHAR | 100 | NOT NULL | Title of the show or movie |
| Release_Date | DATE | - | NOT NULL | Original release date |
| Rating | VARCHAR | 10 | NOT NULL | Content rating (G, PG, PG-13, R, TV-14, TV-MA, TV-PG) |
| Director | VARCHAR | 100 | NOT NULL | Director's name |
| Length | INTEGER | - | NOT NULL | Duration in minutes |
| Genre | VARCHAR | 30 | NOT NULL | Primary genre classification |
| Access_Group | VARCHAR | 20 | NOT NULL, CHECK IN ('Basic', 'Premium') | Required subscription level |
| Cost_To_Buy | DECIMAL | 5,2 | NULL | Purchase price for basic users (NULL for Basic tier shows) |

### BUYS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Buy_ID | INTEGER | - | PRIMARY KEY, AUTOINCREMENT | Unique purchase transaction identifier |
| User_ID | INTEGER | - | FOREIGN KEY, NOT NULL | References CUSTOMERS(User_ID) |
| Show_ID | INTEGER | - | FOREIGN KEY, NOT NULL | References SHOWS(Show_ID) |
| Buy_Date | DATE | - | NOT NULL | Date of purchase transaction |
| Cost | DECIMAL | 5,2 | NOT NULL | Amount charged for this purchase |

### STATISTICS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Date | DATE | - | PRIMARY KEY, NOT NULL | Date for statistics record |
| Total_Shows_Bought | INTEGER | - | DEFAULT 0 | Count of all purchase transactions |
| Total_Subscriptions | INTEGER | - | DEFAULT 0 | Total active subscriptions |
| Premium_Subscriptions | INTEGER | - | DEFAULT 0 | Count of premium subscribers |
| Basic_Subscriptions | INTEGER | - | DEFAULT 0 | Count of basic subscribers |
| Total_Users | INTEGER | - | DEFAULT 0 | Total registered users |
| Last_Updated | DATETIME | - | NOT NULL | Last statistics update timestamp |

### FINANCIALS Table
| Field Name | Data Type | Length | Constraints | Description |
|------------|-----------|---------|-------------|-------------|
| Date | DATE | - | PRIMARY KEY, NOT NULL | Date for financial record |
| Total_Revenue_Buys | DECIMAL | 10,2 | DEFAULT 0.00 | Revenue from show purchases |
| Total_Revenue_Subscriptions | DECIMAL | 10,2 | DEFAULT 0.00 | Revenue from all subscriptions |
| Premium_Subscription_Revenue | DECIMAL | 10,2 | DEFAULT 0.00 | Revenue from premium subscriptions ($80 each) |
| Basic_Subscription_Revenue | DECIMAL | 10,2 | DEFAULT 0.00 | Revenue from basic subscriptions ($30 each) |
| Total_Combined_Revenue | DECIMAL | 10,2 | DEFAULT 0.00 | Sum of all revenue streams |
| Last_Updated | DATETIME | - | NOT NULL | Last financial update timestamp |

## Database Queries

### User Management Queries

1. **Find all premium subscribers**
   - Query the CUSTOMERS table to retrieve users with Premium subscription level
   - Returns user details for premium customer analysis

2. **Get high-value customers**
   - Query CUSTOMERS table filtering by Total_Spent above threshold
   - Orders by spending amount for VIP customer identification

3. **List customers by favorite genre**
   - Query CUSTOMERS table for users with Marketing_Opt_In enabled
   - Groups by Favourite_Genre for targeted marketing campaigns

### Content Management Queries

4. **Get shows available to basic users**
   - Query SHOWS table filtering by Access_Group equals 'Basic'
   - Returns content accessible without additional purchase

5. **Find premium shows with pricing**
   - Query SHOWS table for Premium access group with Cost_To_Buy values
   - Lists purchasable content for basic subscribers

6. **Search shows by multiple criteria**
   - Query SHOWS table with filters for genre, rating, release year, or name
   - Supports advanced content discovery and filtering

### Purchase Analysis Queries

7. **Get user's purchased content**
   - Join BUYS with SHOWS tables filtered by User_ID
   - Returns complete purchase history with show details

8. **Calculate daily purchase revenue**
   - Sum Cost field from BUYS table for specific date
   - Provides daily purchase revenue totals

9. **Find most popular purchased shows**
   - Join BUYS with SHOWS, group by Show_ID, count purchases
   - Identifies trending premium content

### Complex Relationship Queries

10. **Get complete user profile with purchases**
    - Join CUSTOMERS with BUYS and SHOWS tables
    - Returns comprehensive user activity and spending patterns

11. **Find users without purchases**
    - Left join CUSTOMERS with BUYS to find users with no purchase history
    - Identifies potential customers for marketing campaigns

12. **Calculate subscription conversion rates**
    - Compare basic vs premium subscription counts with purchase activity
    - Analyzes subscription tier effectiveness

### Administrative Reporting Queries

13. **Generate current system statistics**
    - Query latest STATISTICS record by Date
    - Provides real-time system metrics dashboard

14. **Create revenue trend analysis**
    - Query FINANCIALS table over date range
    - Shows revenue growth and seasonal patterns

15. **Find inactive user accounts**
    - Query CUSTOMERS for users with empty Shows field
    - Identifies dormant accounts needing engagement

### Financial Analysis Queries

16. **Validate subscription revenue calculations**
    - Cross-reference CUSTOMERS subscription counts with FINANCIALS revenue
    - Ensures financial data accuracy

17. **Track daily revenue breakdown**
    - Query FINANCIALS for subscription vs purchase revenue comparison
    - Shows revenue stream distribution

18. **Calculate average customer value**
    - Average Total_Spent from CUSTOMERS grouped by subscription level
    - Determines customer lifetime value by tier

### Security and Data Integrity Queries

19. **Verify user show collections**
    - Cross-reference CUSTOMERS Shows field with actual BUYS records
    - Ensures data consistency between purchase history and collections

20. **Audit admin access logs**
    - Query ADMIN_CREDENTIALS for recent access patterns
    - Monitors administrative activity for security