SQL Date Functions, CASE Statements, and NULL Handling: 20 Real Interview Questions

SQL date functions fundamentals enable temporal analysis essential to business intelligence. Date manipulation—calculating age, finding quarters, extracting years, computing date differences—appears in nearly every analytics query. Combined with CASE statements for conditional logic and NULL handling for data quality, these are the building blocks of professional SQL.

Understanding SQL Date Functions Fundamentals

SQL date functions fundamentals cover extracting components from dates (YEAR, MONTH, DAY), calculating differences (DATEDIFF), adding/subtracting intervals (DATE_ADD, DATE_SUB), and formatting output (DATE_FORMAT). Each database has different syntax—MySQL, PostgreSQL, SQL Server, Oracle all vary significantly.

Key Date Functions: NOW, CURDATE, DATEADD

SQL date functions fundamentals include NOW() (current timestamp), CURDATE() (current date), DATEADD(day, 30, date_column) adds days. Common patterns: age = YEAR(NOW()) – YEAR(birth_date), quarter = QUARTER(order_date), days_ago = DATEDIFF(NOW(), date_column).

CASE Statements: Conditional Logic

SQL CASE fundamentals teach conditional logic without updating tables. CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE value3 END. Used for categorization: CASE WHEN amount > 1000 THEN Premium WHEN amount > 500 THEN Standard ELSE Basic END. Critical for segment creation and scoring.

NULL Handling: COALESCE, IFNULL, ISNULL

SQL NULL handling fundamentals require understanding that NULL represents unknown, not zero or empty string. IS NULL checks for unknown. COALESCE(col1, col2, default) returns first non-null. IFNULL/ISNULL are database-specific shortcuts. NULL in arithmetic operations propagates: NULL + 5 = NULL, breaking aggregations.

Combining Date Functions with CASE and NULL

SQL date fundamentals combine these concepts: CASE WHEN date_column IS NULL THEN No date WHEN DATEDIFF(NOW(), date_column) > 365 THEN Old ELSE Recent END. Managing NULLs prevents silent data quality issues—unintentional exclusions in reports.

Date Arithmetic and Performance

SQL date functions fundamentals include performance implications. DATE_ADD(column, interval) cant use indexes efficiently. Storing pre-calculated fields (year, quarter, month) as separate columns improves performance on large tables. Alternatively, create indexes on computed columns in some databases.

5 Complete Interview Questions with Solutions

Question 1: Calculate Customer Age and Cohort

Birth dates in customers table. Calculate age in years and assign cohort: 0-18, 19-35, 36-50, 51+. Use DATEDIFF and CASE.

SELECT customer_id,
       YEAR(NOW()) - YEAR(birth_date) AS age,
       CASE WHEN YEAR(NOW()) - YEAR(birth_date) < 19 THEN 0-18
            WHEN YEAR(NOW()) - YEAR(birth_date) <= 35 THEN 19-35
            WHEN YEAR(NOW()) - YEAR(birth_date) <= 50 THEN 36-50
            ELSE 51+ END AS cohort
FROM customers
WHERE birth_date IS NOT NULL;

Explanation: YEAR(NOW()) - YEAR(birth_date) calculates approximate age (doesnt account for birthday in current year). CASE assigns cohorts. WHERE birth_date IS NOT NULL handles missing dates. This SQL date fundamentals pattern appears in every customer analytics query.

Google – Senior Data AnalystAmazon – Analytics EngineerMeta – BI Engineer

Question 2: Extract Quarter and Year, Flag Recent Dates

Orders with order_date. Extract quarter/year, flag orders from last 90 days. Handle NULL dates.

SELECT order_id,
       COALESCE(DATE_FORMAT(order_date, %Y-Q%q), Unknown) AS quarter_year,
       CASE WHEN order_date IS NULL THEN Missing
            WHEN DATEDIFF(NOW(), order_date) <= 90 THEN Recent
            ELSE Older END AS recency
FROM orders;

Explanation: QUARTER(date) extracts quarter, YEAR(date) extracts year. DATE_FORMAT combines them. COALESCE handles NULL dates. DATEDIFF calculates days since order. This SQL date fundamentals pattern segments customers by recency.

Microsoft – Analytics EngineerAirbnb – Senior Data AnalystFlipkart – Data Analyst

Question 3: NULL Handling with COALESCE and Arithmetic

Discount may be NULL. Calculate final_price = price × (1 - discount). COALESCE prevents NULL propagation.

SELECT order_id, price, discount,
       price * (1 - COALESCE(discount, 0)) AS final_price,
       CASE WHEN discount IS NULL THEN No discount
            WHEN discount > 0.2 THEN High discount
            ELSE Standard discount END AS discount_category
FROM orders;

Explanation: NULL * anything = NULL, breaking calculations. COALESCE(discount, 0) replaces NULL with 0 (no discount). CASE separately flags missing discounts. Critical NULL handling pattern for financial calculations.

LinkedIn – Senior Analytics EngineerOYO – BI Engineer

Question 4: Date Ranges and Relative Dates

Find orders from last 12 months. Use DATE_SUB or DATE_ADD for dynamic range. Handle timezone differences.

SELECT COUNT(*) AS orders_last_12_months
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
  AND order_date IS NOT NULL;

Explanation: DATE_SUB(NOW(), INTERVAL 12 MONTH) = today minus 12 months. Dynamic queries dont break with new months. WHERE order_date IS NOT NULL prevents NULLs from appearing in date ranges. This SQL date fundamentals pattern works cross-database with minor syntax changes.

Amazon – Senior BI EngineerGoogle – Analytics Engineer

Question 5: Complex CASE with Multiple Conditions

Segment customers: Premium (90+ days recency AND >$5K LTV), Standard (others with orders), Inactive (no recent orders).

SELECT c.customer_id,
       CASE WHEN DATEDIFF(NOW(), COALESCE(MAX(o.order_date), 1900-01-01)) <= 90
                 AND SUM(COALESCE(o.amount, 0)) > 5000
            THEN Premium
            WHEN MAX(o.order_date) IS NOT NULL THEN Standard
            ELSE Inactive END AS segment
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

Explanation: Complex CASE with nested aggregates and date logic. COALESCE prevents NULL issues. AND combines multiple conditions. This SQL fundamentals pattern (dates + CASE + NULL handling) appears in real customer segmentation.

Stripe – Data AnalystMicrosoft – Senior Data Analyst

15 Practice Questions (Test Yourself)

Question 6: When calculating customer age using YEAR(NOW()) - YEAR(birth_date), does this approach give you a precise age or is it an approximation that doesnt account for whether the birthday has already occurred in the current year?

Google – Data AnalystAmazon – Senior Analytics Engineer

Question 7: When using DATE_SUB to add months with INTERVAL 1 MONTH on January 31st, what date would you get on February since that month doesnt have 31 days, and how should you handle these edge cases?

Meta – BI EngineerFlipkart – Analytics Engineer

Question 8: When you use COALESCE to check multiple columns looking for the first non-NULL value, does the order in which you list the columns matter, and how does this affect the outcome of the function?

Airbnb – Senior Data AnalystLinkedIn – Data AnalystOYO – Senior BI Engineer

Question 9: When you have nested CASE statements where outer CASE conditions check for NULL values before inner CASE conditions evaluate more complex logic, how does this improve data quality handling?

Google – Senior BI EngineerAmazon – Analytics Engineer

Question 10: When using DATEDIFF in different databases like MySQL and SQL Server, does the parameter order stay consistent, and how do you ensure your queries work across multiple database systems?

Microsoft – Analytics EngineerStripe – Data AnalystMeta – Senior Analytics Engineer

Question 11: If you need to check whether a column value is NULL versus when using IFNULL, ISNULL, or COALESCE functions, which approach would perform best when handling 100 million rows of data?

Amazon – Data AnalystGoogle – Senior Analytics Engineer

Question 12: When using CASE with SUM aggregation like SUM(CASE WHEN condition THEN amount ELSE 0 END), how does this approach compare to using traditional pivot table syntax for creating cross-tabulated results?

Flipkart – Senior Data AnalystLinkedIn – Analytics EngineerAirbnb – BI Engineer

Question 13: When creating a WHERE clause with functions like WHERE YEAR(order_date) = 2024, can the database still use indexes on the date column effectively, or should you avoid functions on indexed columns?

OYO – Analytics EngineerGoogle – BI Engineer

Question 14: When using COUNT with CASE like COUNT(CASE WHEN condition THEN 1 END), how does this differ from using a regular COUNT(*) combined with a WHERE clause in terms of functionality and performance?

Meta – Data AnalystAmazon – Senior BI EngineerStripe – Analytics Engineer

Question 15: When using GROUP BY with a date column that contains NULL values, how does SQL treat the NULL entries when grouping, and do they form their own group?

Microsoft – Analytics EngineerFlipkart – BI Engineer

Question 16: When creating age groups or ranges using CASE like 18-24, 25-34, how would you compare this manual approach to using a window function like NTILE that automatically creates equal-sized buckets?

Google – Senior Analytics EngineerLinkedIn – Data AnalystAmazon – BI Engineer

Question 17: When writing a CASE statement that checks whether a column is NULL using CASE WHEN col = NULL, why does this fail, and what is the correct syntax for checking NULL values?

Airbnb – Senior Data AnalystOYO – Analytics Engineer

Question 18: When using DATE_FORMAT to output dates in different formats, would different date values sometimes result in different length output strings, and how could this affect string comparisons?

Meta – Senior Analytics EngineerGoogle – BI EngineerMicrosoft – Data Analyst

Question 19: When you have multiple COALESCE functions nested like COALESCE(col1, COALESCE(col2, col3)), would it be more efficient to flatten this into a single COALESCE with all columns listed together?

Stripe – Data AnalystFlipkart – Senior Analytics Engineer

Question 20: When you have a CASE statement with more than ten conditions and youre using multiple date, string, and NULL functions together, when would it be better to refactor this into a separate CTE for improved readability?

Amazon – Analytics EngineerLinkedIn – Senior BI EngineerGoogle – Senior Data Analyst

Ready to Master SQL Date Functions & All Analytics Concepts?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.