Must Know Data Interview Question| SALES KPI | CAST & CASE STATEMENT | INTERVIEW TIPS | SQL | PART 3

Welcome to series on Data Interview questions and answers! In this video, we'll cover the most asked and most common data questions that help you prepare nextgen data professionals for data engineer, data analyst, & BIE position. We'll explore technical and real-life case-based questions covering SQL, DAX, POWER BI, Python, ETL, data warehousing, machine learning, Microsoft Fabric, Azure & Snowflake Cloud platforms, Big Data frameworks (Databricks, Spark). Want more similar videos- hit like, comment, share and subscribe ❤️Do Like, Share and Comment ❤️ Like Aim 1000 likes! ➖➖➖➖➖➖➖➖➖➖➖➖➖ Please like & share the video. ➖➖➖➖➖➖➖➖➖➖➖➖➖ Sample Data script -- ====================================== -- Cloud Subscription Business Example -- ====================================== -- Drop tables if they exist DROP TABLE IF EXISTS subscriptions; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS customer; -- Create customers table CREATE TABLE customer ( customer_id INT PRIMARY KEY, company_name VARCHAR(100), country VARCHAR(50) ); -- Create products table CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), tier VARCHAR(50) ); -- Create subscriptions table CREATE TABLE subscriptions ( subscription_id INT PRIMARY KEY, customer_id INT, product_id INT, start_date DATE, end_date DATE, is_active VARCHAR(10), auto_renew_flg TINYINT, billing_cycle VARCHAR(10), FOREIGN KEY (customer_id) REFERENCES customer(customer_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); -- Insert sample customers INSERT INTO customer VALUES (1, 'Acme Corp', 'USA'), (2, 'GlobalSoft', 'Germany'), (3, 'Cloudify', 'Canada'), (4, 'TechNova', 'USA'); -- Insert sample products INSERT INTO products VALUES (1, 'CloudDrive', 'Basic'), (2, 'CloudCompute', 'Pro'), (3, 'CloudSecure', 'Enterprise'); -- Insert sample subscriptions INSERT INTO subscriptions VALUES (101, 1, 1, '2023-01-01', '2024-01-01', 'TRUE', 1, 'annual'), (102, 2, 2, '2023-06-15', '2024-06-15', 'TRUE', 1, 'monthly'), (103, 3, 1, '2023-03-01', '2024-03-01', 'FALSE', 0, 'annual'), (104, 4, 3, '2022-09-01', '2023-09-01', 'TRUE', 1, 'annual'), (105, 1, 2, '2023-12-01', '2024-12-01', 'TRUE', 0, 'monthly'), (106, 2, 3, '2023-02-01', '2024-02-01', 'TRUE', 1, 'annual'); /* ➖➖➖➖➖➖➖➖➖➖➖➖➖ what we have covered in this video: CONDITIONAL AGGREGATE DATA SQL Problem Statement - Solved using CASE STATEMENT, WHERE CLAUSE, COUNT & CAST FUNCTION (MEDIUM & ADVANCE SQL DATA INTERVIEW CONCEPTS) We also looked as some tips and strategies for the standing out in your next data interview! Summary: MOST ASKED AND MOST IMPORTANT MUST KNOW INTERVIEW PROBLEM STATEMENT - A cloud services company offers subscription-based software to businesses. Customers can subscribe to products with different tiers (Basic, Pro, Enterprise). Subscriptions can be auto-renewing and billed monthly or annually. The business wants to measure how many of their **active subscriptions** are both **auto-renewing** and billed on an **annual** cycle to assess recurring revenue reliability.Tracking auto-renew + annual billing is crucial for recurring revenue forecasting because it directly reflects the stability, predictability, and health of a SaaS or subscription-based business model. What percentage of all **active subscriptions** are both **auto-renewing** and **billed annually**? CONDITIONAL AGGREGATIONS QUESTIONS CATEGORY TIP 1 - Use CASE Statement & COUNT/SUM Aggregate Function (Use Case: When you want to sum/count only certain rows within an aggregate function) TIP 2 - IDENTIFY KEYWORDS Words like 'Percent' or subset of the group required indicate need CASE Statement and Count/Sum aggregate function TIP 3 - Ask FOLLOW UP questions to the interviewer regarding the data & output. Do they need upto which format? TIP 4 - STAND OUT!! - Walkthrough the code and explain why you used COUNT/SUM & if required know how to apply formatting as well! ➖➖➖➖➖➖➖➖➖➖➖➖➖ Hope this video was useful and you learned something new :) See you in next video, until then Bye!