SQL Programming Resources on Telegram: “WITH monthly_sales AS ( SELECT DATE

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders GROUP BY DATE_TRUNC('month', order_date)
),
monthly_comparison AS (
    SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS previous_revenue
    FROM monthly_sales
)
SELECT
    month,
    revenue,
    previous_revenue,
    (revenue - previous_revenue) * 100.0 / NULLIF(previous_revenue, 0) AS mom_growth
FROM monthly_comparison
ORDER BY month;

⏳ 10. Calculating Date Differences

A common business question is: How many days were there between two dates?

PostgreSQL:

SELECT order_id, delivery_date - order_date AS delivery_days FROM orders;

Other databases may use DATEDIFF():

SELECT order_id, DATEDIFF(day, order_date, delivery_date) AS delivery_days FROM orders;

The exact syntax depends on the database.

🚚 11. Delivery Time Analysis

We can calculate delivery duration:

SELECT order_id, order_date, delivery_date, delivery_date - order_date AS delivery_duration FROM orders;

This can help answer: Which orders took longer than the target SLA?

🎯 12. SLA Analysis

Suppose the target delivery time is 3 days.

SELECT
    order_id,
    order_date,
    delivery_date,
    CASE WHEN delivery_date - order_date <= 3 THEN 'Within SLA' ELSE 'SLA Breach' END AS sla_status
FROM orders;

🧮 13. Calculating Ageing

Ageing is common in Banking, Finance, Accounts payable, Receivables, Operations, Ticket management.

You can calculate days overdue:

SELECT invoice_id, due_date, CURRENT_DATE - due_date AS days_overdue
FROM invoices
WHERE due_date < CURRENT_DATE;

💰 14. Ageing Buckets

We can combine date calculations with CASE.

SELECT
    invoice_id,
    due_date,
    CASE
        WHEN CURRENT_DATE - due_date <= 0 THEN 'Not Due'
        WHEN CURRENT_DATE - due_date <= 30 THEN '1-30 Days'
        WHEN CURRENT_DATE - due_date <= 60 THEN '31-60 Days'
        WHEN CURRENT_DATE - due_date <= 90 THEN '61-90 Days'
        ELSE '90+ Days'
    END AS ageing_bucket
FROM invoices;

📆 15. Filtering by Date

Suppose we want orders from 2026:

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

This half-open date range is especially useful when order_date is a timestamp.

⚠️ 16. Why BETWEEN Can Be Risky with Timestamps

Suppose order_timestamp contains 2026-01-31 15:30:00.

Using:

WHERE order_timestamp BETWEEN '2026-01-01' AND '2026-01-31'

may not include all records on January 31.

A safer pattern is:

WHERE order_timestamp >= '2026-01-01' AND order_timestamp < '2026-02-01'

🕐 17. Extracting the Hour

For timestamp data, you may want to analyze activity by hour.

SELECT EXTRACT(HOUR FROM transaction_timestamp) AS transaction_hour, COUNT(*) AS transaction_count
FROM transactions GROUP BY EXTRACT(HOUR FROM transaction_timestamp) ORDER BY transaction_hour;

This can help identify peak transaction times, system load, customer activity patterns.

📅 18. Day-of-Week Analysis

You can also analyze transactions by weekday.

Some systems provide EXTRACT(DOW FROM transaction_date) or DAYOFWEEK().

SELECT EXTRACT(DOW FROM transaction_date) AS day_of_week, COUNT(*) AS transactions
FROM transactions GROUP BY EXTRACT(DOW FROM transaction_date) ORDER BY day_of_week;

🏪 19. Weekend vs Weekday Analysis

Using date functions with CASE: