Objective

The goal of this question is to design a SQL table and query to track and retrieve the correct Netflix billing tier for each customer.

Prerequisites:

1. A table called NetflixCustomers exists, which contains the current active subscription tier of all clients.

CREATE TABLE NetflixCustomers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    SubscriptionTier VARCHAR(20)
);

2. There are 3 different kinds of tiers: Basic, Standard, Premium

Expected Outcome:

1. Design a table to record when each tier was activated for each customer.
2. Write a SQL query to fetch the customer list along with the tier each customer should be billed for.
3. Tier history needs to be maintained in the db.

Constraints:

1. Billing is based on the highest tier enabled within a given month. If a customer upgrades to Standard or Premium at any time during the month, they are billed at the highest tier reached. This rule applies even if they later downgrade within the same month.
2. Once a tier is enabled, it remains active unless the customer upgrades or downgrades.
3. Customers can switch to any tier at any time.

Results:

1. The SQL query should return a table with the CustomerID and the Tier they should be billed for based on the start date of the specified month.

Test cases

Test Case 1: Basic Subscription with No Changes

Scenario: A customer has only the Basic tier active for the entire month.
Expectation: The billing should be for the Basic tier, as no changes occurred.

Test Case 2: Upgrade to Higher Tier Mid-Month

Scenario: A customer starts with Basic, upgrades to Standard on the 10th of the month, then to Premium on the 20th of the same month.
Expectation: The billing should be for the Premium tier because it’s the highest tier reached within the month.

Test Case 3: Downgrade within the Month

Scenario: A customer starts with Premium, downgrades to Standard on the 15th of the month, and finally to Basic on the 25th.
Expectation: The billing should still be for the Premium tier, as that was the highest tier reached in the month.

Test Case 4: Multiple Upgrades and Downgrades

Scenario: A customer starts with Basic, upgrades to Premium on the 5th, downgrades to Standard on the 15th, and then downgrades to Basic on the 25th.
Expectation: Billing should be for Premium, since it’s the highest tier reached during the month.

Test Case 5: No Changes from Previous Month

Scenario: A customer was on the Standard tier in the previous month and made no changes this month.
Expectation: The billing should remain at the Standard tier for the current month.

Test Case 6: Upgrade on Last Day of the Month

Scenario: A customer is on Basic for the entire month and upgrades to Premium on the last day of the month.
Expectation: Billing should be for the Premium tier, as that’s the highest tier reached in the month.

Test Case 7: Downgrade on First Day of the Month

Scenario: A customer starts on Premium but downgrades to Standard on the 1st of the month.
Expectation: Billing should still be for Premium, since it was the active tier at the start of the month.

Test Case 8: Single-Tier Upgrade Across Multiple Months

Scenario: A customer starts on Basic, upgrades to Standard on the 15th, and makes no changes in the next month.
Expectation: Billing for the current month should be Standard, and for the next month, it should remain Standard unless another change is made.

Test Case 9: New Customer in Mid-Month

Scenario: A new customer joins on the 15th and starts with a Standard tier.
Expectation: Billing should be for Standard, as there were no other changes during the month.

Test Case 10: Customer with No Active Tier at All

Scenario: A customer has no active tier for the entire month due to a subscription pause or cancellation.
Expectation: The billing should reflect that there’s no active tier, or show "None."

Test Case 11: Back-to-Back Upgrades and Downgrades on Same Day

Scenario: A customer upgrades to Premium in the morning and downgrades back to Basic in the evening of the same day.
Expectation: Billing should still be for Premium, as it was the highest tier reached during the month.

Note: This is a self-exercise question and is not sourced from any interview