SQL Programming Resources on Telegram: “SELECT transaction

SELECT transaction_date,
    CASE WHEN EXTRACT(DOW FROM transaction_date) IN (0, 6) THEN 'Weekend' ELSE 'Weekday' END AS day_type
FROM transactions;

📊 20. Business Example — Peak Sales Day

Suppose we want to find which date generated the highest revenue.

WITH daily_sales AS (
    SELECT order_date, SUM(amount) AS daily_revenue FROM orders GROUP BY order_date
)
SELECT order_date, daily_revenue FROM daily_sales ORDER BY daily_revenue DESC LIMIT 1;

🧠 21. Date Truncation vs Date Extraction

Extraction gets a component: EXTRACT(YEAR FROM order_date) → 2026

Truncation converts to a larger bucket: DATE_TRUNC('month', order_date) → 2026-09-01

• EXTRACT → Give the part

• DATE_TRUNC → Give the period bucket

🔄 22. Adding Intervals to Dates

Sometimes you need to calculate a future or previous date.

For example, PostgreSQL:

SELECT order_date, order_date + INTERVAL '7 days' AS follow_up_date FROM orders;

This can be useful for follow-up dates, SLA deadlines, subscription periods, reminder dates.

📉 23. Finding Inactive Customers

One approach is:

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

Then filter:

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

SELECT customer_id, last_order_date FROM customer_activity WHERE last_order_date < CURRENT_DATE - INTERVAL '90 days';

📈 24. Year-over-Year Analysis

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)
)

SELECT month, revenue, LAG(revenue, 12) OVER (ORDER BY month) AS previous_year_revenue
FROM monthly_sales;

Here LAG(..., 12) looks 12 rows backward. If months are missing, LAG(..., 12) may not correspond to same calendar month one year earlier. For robust YoY, explicitly align dates or build a complete calendar.

📅 25. Calendar Tables

A calendar table contains one row for each date in a period.

Example:

• 2026-01-01 | 2026 | 1 | January | Thursday

• 2026-01-02 | 2026 | 1 | January | Friday

Calendar tables are extremely useful for time-series analysis, missing-date detection, reporting, fiscal calendars, holidays, business days, YoY comparisons.

🏦 26. Financial Year Analysis

Business calendars don't always follow January–December. For example April → March. You can create fiscal-year logic using date functions and CASE.

Conceptually:

CASE
    WHEN EXTRACT(MONTH FROM order_date) >= 4 THEN EXTRACT(YEAR FROM order_date) + 1
    ELSE EXTRACT(YEAR FROM order_date)
END AS fiscal_year