Date, string and numeric functions

Date functions

Dates drive almost every business query: monthly trends, year-over-year, days to convert. Each database names date functions differently — the concepts are identical.

Monthly completed orders in 2025:

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed' AND order_date >= '2025-01-01'
GROUP BY month
ORDER BY month;

The December peak and February dip are seasonality — something to remember when comparing months.

Days from signup to first order (customers who ordered at least once):

WITH first_order AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders GROUP BY customer_id
)
SELECT ROUND(AVG(julianday(f.first_order_date) - julianday(c.signup_date)), 1) AS avg_days_to_first_order
FROM customers c JOIN first_order f ON f.customer_id = c.customer_id
WHERE f.first_order_date >= c.signup_date;

String functions

Cleaning the messy city column in customers_raw:

SELECT city                    AS raw_city,
       UPPER(SUBSTR(TRIM(city), 1, 1)) || LOWER(SUBSTR(TRIM(city), 2)) AS clean_city,
       COUNT(*) AS rows
FROM customers_raw
WHERE TRIM(LOWER(city)) = 'baku'
GROUP BY city;

Four spellings of one city collapse into one after TRIM and case normalisation. Extracting the email domain:

SELECT SUBSTR(email, INSTR(email, '@') + 1) AS domain, COUNT(*) AS customers
FROM customers GROUP BY domain;

Numeric functions

• ROUND(x, n) rounds to n decimals (round only for display, at the end).
• CAST(x AS REAL) / CAST(x AS NUMERIC(10,2)) changes type.
• ABS(x), CEIL/FLOOR (PostgreSQL, SQL Server; SQLite 3.35+ via math functions).
• Safe division: x / NULLIF(y, 0) returns NULL instead of a division-by-zero error.

SELECT store_id,
       ROUND(100.0 * SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2) AS return_rate_pct
FROM orders
GROUP BY store_id
ORDER BY return_rate_pct DESC;

Practice

Summary

• Date bucketing (strftime, DATE_TRUNC) powers trends; durations come from date differences; use ranges, not equality, for timestamps.
• String functions clean and standardise text before grouping.
• Round at the end; use NULLIF to avoid division by zero; cast when types are wrong.