⚠️ 27. Time Zones Matter

A timestamp may represent UTC while business operates in IST.

• UTC: 2026-09-18 23:30

• India: 2026-09-19 05:00

For global analytics, always understand source timezone, database timezone, reporting timezone, daylight-saving rules.

🎤 SQL Interview Questions

Q1. What is the difference between DATE and TIMESTAMP?

DATE generally stores a calendar date. TIMESTAMP generally stores both date and time.

Q2. How do you extract the year from a date?

A common approach is EXTRACT(YEAR FROM date_column). Some databases also support YEAR(date_column).

Q3. How do you calculate the current date?

CURRENT_DATE

Q4. What is DATE_TRUNC() used for?

It truncates a date/time to a specified period such as year, month, day, hour. Commonly used for time-based grouping.

Q5. How can you calculate month-over-month growth?

Typically aggregate by month → LAG() previous month → calculate percentage difference.

Q6. Why can BETWEEN be problematic with timestamps?

Because an upper bound such as '2026-01-31' may represent the start of that date rather than entire day.

Q7. What is a calendar table?

A table containing dates and associated attributes such as year, month, weekday, fiscal period, holidays.

Q8. How can you calculate customer inactivity?

Find the customer's latest transaction date using MAX(transaction_date) and compare with current date.

Q9. What is LAG() useful for in time-series analysis?

It allows comparison with a previous row, such as previous month revenue.

Q10. Why are time zones important in analytics?

Same timestamp can represent different local dates and times depending on timezone.

📝 Practice Questions

Practice 1 — Calculate annual revenue.

SELECT EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS revenue FROM orders GROUP BY EXTRACT(YEAR FROM order_date) ORDER BY year;

Practice 2 — Calculate monthly revenue.

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;

Practice 3 — Find the last order date for every customer.

SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id;

Practice 4 — Find orders placed in 2026.

SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Practice 5 — Calculate delivery duration.

SELECT order_id, delivery_date - order_date AS delivery_duration FROM orders;

🧪 Mini SQL Challenge

You have: orders (order_id, customer_id, order_date, delivery_date, amount)

Create a query that returns Order ID, Customer ID, Order month, Order amount, Delivery duration, SLA status, Running customer revenue. Assume SLA is 3 days.

Solution:

SELECT
    order_id,
    customer_id,
    DATE_TRUNC('month', order_date) AS order_month,
    amount,
    delivery_date - order_date AS delivery_duration,
    CASE WHEN delivery_date - order_date <= 3 THEN 'Within SLA' ELSE 'SLA Breach' END AS sla_status,
    SUM(amount) OVER (
        PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_customer_revenue
FROM orders;

💡 Double Tap ❤️ For More