This is a copy-paste reference: dozens of practical SQL examples grouped by the question you are actually asking - "How do I filter by a list of values?", "How do I get the top N per group?", "How do I turn rows into columns?" Every entry has a short task, one or two lines of context, a runnable query, and, for the ones that matter, the result it produces. Every example runs against the same small sample schema shown below.
What this library covers
Bookmark this page. If you want the terse one-page version of the syntax, keep the SQL cheat sheet open in another tab; if you want to go deeper on any single area, the section intros link out to fuller guides such as joins, advanced functions, and advanced queries.
1. The sample schema (used by every example)
Five tables model a tiny e-commerce shop: customers place orders, each order has order_items that reference products, and employees report to other employees. Create these once and every query below will run as written. The dialect is generic ANSI SQL; where a vendor differs (mostly MySQL vs PostgreSQL) a note calls it out.
-- Customers who place orders
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(160),
country VARCHAR(2),
signup_date DATE
);
-- One row per order (header)
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id),
status VARCHAR(20), -- pending / paid / shipped / cancelled
total DECIMAL(10,2),
ordered_at TIMESTAMP
);
-- Line items belonging to an order
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT,
unit_price DECIMAL(10,2)
);
-- Catalogue
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(120),
category VARCHAR(50),
price DECIMAL(10,2)
);
-- Staff, with a self-referencing manager link
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT REFERENCES employees(id),
hire_date DATE,
salary DECIMAL(10,2)
);How to use this page: read the H3 titles as questions. Scan until one matches your task, copy the query, and swap in your own table and column names. The queries are correct in isolation; adapt filters and column lists to your real data.
2. Selecting & filtering
Almost every query starts here: pick columns, keep the rows you want, throw away the rest. The data query language essentials below cover distinct values, list and range filters, text patterns, and the ever-tricky NULL. A good habit from the start is to select only the columns you need rather than SELECT *: it makes the intent obvious, keeps result sets small, and lets the database serve some queries entirely from an index.
How do I select and rename specific columns?
Name the columns you want and give them friendly output names with AS. The alias is what your application code and downstream reports will see.
SELECT id AS order_id,
customer_id AS customer,
total AS amount
FROM orders;How do I get the distinct values of a column?
Use DISTINCT to collapse duplicate rows. Here we list every country we have a customer in, once each.
SELECT DISTINCT country
FROM customers
ORDER BY country;| country |
|---|
| AE |
| GB |
| US |
How do I count how many distinct values there are?
Wrap the column in COUNT(DISTINCT ...) to get the number of unique values rather than the rows themselves.
SELECT COUNT(DISTINCT country) AS countries
FROM customers;How do I filter by a list of values?
IN is cleaner than chaining OR conditions and reads exactly like the requirement.
SELECT id, status, total
FROM orders
WHERE status IN ('paid', 'shipped');How do I exclude a list of values?
NOT IN is the mirror image. Be careful: if the list can contain NULL, NOT IN returns no rows - use NOT EXISTS instead when nulls are possible.
SELECT id, status
FROM orders
WHERE status NOT IN ('cancelled', 'pending');How do I filter by a date range?
BETWEEN is inclusive on both ends. For timestamps, prefer a half-open range (>= start, < next day) so you do not accidentally miss or double-count the boundary.
-- All of Q1 2026, boundary-safe
SELECT id, ordered_at, total
FROM orders
WHERE ordered_at >= '2026-01-01'
AND ordered_at < '2026-04-01';How do I match a text pattern?
LIKE with % (any run of characters) and _ (one character) covers most searches. Leading wildcards cannot use an index, so keep them anchored where you can.
-- Gmail addresses, case-insensitive in Postgres via ILIKE
SELECT name, email
FROM customers
WHERE email LIKE '%@gmail.com';How do I search for a substring anywhere in a column?
Wildcards on both sides of the term match it wherever it appears. This is convenient but cannot use a normal B-tree index, so on large tables reach for full-text search instead.
-- Any product whose name mentions "pro"
SELECT id, name
FROM products
WHERE name LIKE '%pro%';How do I combine several conditions with the right precedence?
AND binds tighter than OR, so mixing them without parentheses is a classic source of wrong results. Group the OR branch explicitly.
-- Paid or shipped orders, but only above 100
SELECT id, status, total
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
AND total > 100;How do I handle NULLs safely?
NULL means "unknown" and is never equal to anything, not even another NULL. Test with IS NULL / IS NOT NULL, and substitute a default with COALESCE.
-- Show a fallback label when country is missing
SELECT name,
COALESCE(country, 'unknown') AS country
FROM customers
WHERE country IS NULL
OR country = '';Watch out: country = NULL and country <> 'AE' both silently drop rows where country is NULL. If missing values should count, add OR country IS NULL explicitly.
3. Sorting & pagination
Result order is never guaranteed without ORDER BY - databases are free to return rows in whatever order is convenient, and that order can change between runs. These patterns cover taking the top slice, walking through pages efficiently, and controlling exactly where ties and nulls land. When you paginate, always add a unique tie-breaker column (such as the primary key) to your sort so that rows never shuffle between pages.
How do I get the top N rows?
Sort, then limit. Use LIMIT in MySQL/PostgreSQL/SQLite; SQL Server uses TOP or OFFSET ... FETCH.
-- Five biggest orders
SELECT id, total
FROM orders
ORDER BY total DESC
LIMIT 5;How do I paginate with OFFSET?
The simplest paging is LIMIT plus OFFSET. It is fine for early pages but gets slow deep in a large table because the database must count past every skipped row.
-- Page 3, 20 rows per page (skip 40)
SELECT id, ordered_at
FROM orders
ORDER BY ordered_at DESC, id DESC
LIMIT 20 OFFSET 40;How do I paginate efficiently (keyset / seek)?
Keyset pagination remembers the last row you saw and asks for rows "after" it. It stays fast at any depth because it seeks into an index instead of counting rows.
-- Next page after the last (ordered_at, id) you rendered
SELECT id, ordered_at
FROM orders
WHERE (ordered_at, id) < ('2026-03-10 09:00', 8842)
ORDER BY ordered_at DESC, id DESC
LIMIT 20;How do I sort by more than one column?
List columns in priority order. Each can independently be ASC (default) or DESC.
-- Newest first within each status
SELECT status, id, ordered_at
FROM orders
ORDER BY status ASC, ordered_at DESC;How do I put NULLs last when sorting?
PostgreSQL supports NULLS LAST directly. In MySQL, sort on col IS NULL first to push nulls to the bottom.
-- PostgreSQL
SELECT id, total FROM orders
ORDER BY total DESC NULLS LAST;
-- MySQL equivalent
SELECT id, total FROM orders
ORDER BY (total IS NULL), total DESC;How do I return only the single latest row?
Order by the timestamp descending and take one. For "latest per group" use the ROW_NUMBER dedupe pattern in the window section instead.
SELECT id, customer_id, ordered_at
FROM orders
ORDER BY ordered_at DESC
LIMIT 1;4. Aggregation & grouping
Aggregation reduces many rows to one number per group. The rule to remember: every non-aggregated column in the SELECT must appear in GROUP BY, and row filters go in WHERE while group filters go in HAVING. A useful mental model is that the database first filters rows with WHERE, then forms groups, then computes aggregates, then filters those groups with HAVING, and only finally sorts and limits - which is exactly why you cannot reference an aggregate in WHERE or a column alias before it has been computed.
How do I count rows per group?
Group by the dimension, then count. This is the single most common analytics query.
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;| status | orders |
|---|---|
| paid | 1204 |
| shipped | 986 |
| pending | 143 |
| cancelled | 77 |
How do I sum and average per group?
Combine several aggregates in one pass. ROUND keeps money columns tidy.
SELECT customer_id,
COUNT(*) AS orders,
SUM(total) AS lifetime_value,
ROUND(AVG(total), 2) AS avg_order
FROM orders
GROUP BY customer_id;How do I count only rows that meet a condition?
Put a CASE inside an aggregate. Because COUNT ignores NULL, and SUM of 1/0 adds up the trues, both patterns give conditional counts in the same grouped query.
SELECT customer_id,
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
COUNT(CASE WHEN total > 500 THEN 1 END) AS big_orders
FROM orders
GROUP BY customer_id;How do I get the min, max and range per group?
MIN and MAX work on numbers, dates, and text. Combining them gives the spread of each group in a single pass, which is often more useful than the average alone.
SELECT customer_id,
MIN(total) AS smallest,
MAX(total) AS largest,
MAX(total) - MIN(total) AS spread
FROM orders
GROUP BY customer_id;How do I filter on an aggregate (HAVING)?
WHERE filters rows before grouping; HAVING filters the groups after. Use HAVING whenever the condition references an aggregate.
-- Customers with more than 10 orders
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10;How do I filter rows and groups together?
Use both clauses in one query: WHERE narrows the input, HAVING qualifies the output.
-- Among paid orders only, keep customers who spent over 2000
SELECT customer_id, SUM(total) AS paid_value
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total) > 2000
ORDER BY paid_value DESC;How do I combine several groups at once (GROUP BY multiple columns)?
Grouping on more than one column produces one row per unique combination - here, per country and status.
SELECT c.country, o.status, COUNT(*) AS orders
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.country, o.status
ORDER BY c.country, orders DESC;5. Joins
A join stitches rows from two tables on a matching key; the type decides what happens to rows without a match. INNER keeps only matched pairs, LEFT keeps every left-hand row and pads the missing right side with NULL, and the anti-join idiom (LEFT JOIN then WHERE right.id IS NULL) finds the rows that have no partner at all. For a visual walkthrough see the SQL joins guide.
How do I show each order with its customer?
An INNER JOIN returns only rows that match on both sides - here, orders that have a real customer.
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;How do I find customers with no orders?
An anti-join: LEFT JOIN keeps every customer, then keep only the ones where the order side came back NULL.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;How do I join three (or more) tables?
Chain JOIN ... ON clauses. Here we walk order → item → product to get product-level revenue.
SELECT p.name,
SUM(oi.quantity) AS units,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'paid'
GROUP BY p.name
ORDER BY revenue DESC;How do I join a table to itself (hierarchy)?
A self-join treats one table as two aliases. Here each employee is matched to their manager row.
SELECT e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY manager, employee;How do I list every combination of two sets (cross join)?
A CROSS JOIN pairs every left row with every right row. It is the right tool for building a complete grid, such as every product against every month, so that gaps show up as zeros rather than missing rows.
-- Every product x every category label (illustrative)
SELECT p.name, cats.category
FROM products p
CROSS JOIN (SELECT DISTINCT category FROM products) cats;Aliases matter: when a query touches the same column name in two tables (like id), always qualify columns with the table alias. It removes ambiguity errors and makes the join direction obvious.
6. Subqueries & CTEs
Subqueries let a query use the result of another query. Common Table Expressions (WITH) do the same thing but read top to bottom, which is far easier to maintain. See advanced SQL queries for the deep dive.
How do I test whether related rows exist (EXISTS)?
EXISTS stops at the first matching row, which makes it both fast and clear when you only care whether a match is present, not how many. Here we keep customers who have placed at least one paid order.
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
AND o.status = 'paid'
);How do I use a correlated subquery?
A correlated subquery references the outer row, running once per row. Here we keep orders above that customer's own average.
SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
SELECT AVG(o2.total)
FROM orders o2
WHERE o2.customer_id = o.customer_id
);How do I name a subquery with a CTE?
A WITH clause defines a named, reusable result set. It flattens nested subqueries into readable steps.
WITH customer_totals AS (
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
GROUP BY customer_id
)
SELECT c.name, t.lifetime_value
FROM customer_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.lifetime_value > 5000
ORDER BY t.lifetime_value DESC;How do I walk a hierarchy with a recursive CTE?
A recursive CTE has an anchor row plus a member that references the CTE itself. This expands the full management chain under a given manager.
WITH RECURSIVE org AS (
-- anchor: the top manager
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recurse: everyone reporting to a row already in org
SELECT e.id, e.name, e.manager_id, org.depth + 1
FROM employees e
JOIN org ON org.id = e.manager_id
)
SELECT depth, name FROM org ORDER BY depth, name;7. Window functions
Window functions compute across a set of rows related to the current row without collapsing them, so you keep every detail row and add a calculation beside it. The magic is in the OVER (...) clause: PARTITION BY splits the data into independent groups, ORDER BY sets the direction of travel, and an optional frame narrows the window to a sliding range of rows. Once these three ideas click, running totals, rankings, period-over-period comparisons, and deduplication all become variations on one pattern. If these are new to you, read the dedicated window functions guide and the list of advanced SQL functions.
How do I compute a running total?
SUM(...) OVER (ORDER BY ...) accumulates as it moves through the ordered rows.
SELECT ordered_at, total,
SUM(total) OVER (ORDER BY ordered_at) AS running_total
FROM orders
ORDER BY ordered_at;| ordered_at | total | running_total |
|---|---|---|
| 2026-01-02 | 120.00 | 120.00 |
| 2026-01-03 | 80.00 | 200.00 |
| 2026-01-05 | 240.00 | 440.00 |
How do I get the top N rows per group?
Number rows within each partition, then keep the low numbers. This is the canonical "top 3 orders per customer" pattern.
WITH ranked AS (
SELECT id, customer_id, total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY total DESC
) AS rn
FROM orders
)
SELECT id, customer_id, total
FROM ranked
WHERE rn <= 3;How do I rank rows (with ties)?
RANK leaves gaps after ties (1,1,3); DENSE_RANK does not (1,1,2); ROW_NUMBER never ties. Pick by how you want ties handled.
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;How do I compare each month to the previous one (LAG)?
LAG pulls a value from an earlier row so you can compute a delta without a self-join.
WITH monthly AS (
SELECT DATE_TRUNC('month', ordered_at) AS mth,
SUM(total) AS revenue
FROM orders
GROUP BY 1
)
SELECT mth, revenue,
revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
FROM monthly
ORDER BY mth;How do I get the first or last value in a window?
FIRST_VALUE and LAST_VALUE read a value from the boundary of the window frame. Watch the default frame: for LAST_VALUE you usually need an explicit frame reaching the end of the partition.
SELECT customer_id, ordered_at, total,
FIRST_VALUE(total) OVER w AS first_order_value
FROM orders
WINDOW w AS (
PARTITION BY customer_id ORDER BY ordered_at
);How do I keep one row per group (dedupe)?
Number the duplicates and keep rn = 1. Here we keep each customer's most recent order only.
WITH latest AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at DESC
) AS rn
FROM orders
)
SELECT id, customer_id, ordered_at
FROM latest
WHERE rn = 1;8. Modifying data
Reads are only half the job. These patterns cover inserting in bulk, copying between tables, upserting, updating from a join, and cleaning up duplicate rows. Two habits will save you from painful mistakes: always run the matching SELECT first to confirm exactly which rows an UPDATE or DELETE will touch, and wrap anything non-trivial in a transaction so you can ROLLBACK if the row count looks wrong. A missing WHERE clause on an update is one of the most common ways to ruin a table.
How do I insert many rows at once?
List multiple value tuples after one INSERT - far faster than one statement per row.
INSERT INTO products (id, name, category, price)
VALUES
(1, 'Keyboard', 'peripherals', 45.00),
(2, 'Monitor', 'displays', 180.00),
(3, 'Mouse', 'peripherals', 22.50);How do I insert from a query (INSERT ... SELECT)?
Populate a table from the result of a SELECT - ideal for archive or summary tables.
INSERT INTO order_archive (id, customer_id, total, ordered_at)
SELECT id, customer_id, total, ordered_at
FROM orders
WHERE ordered_at < '2025-01-01';How do I insert-or-update (UPSERT)?
Insert if the key is new, update if it already exists. PostgreSQL and SQLite use ON CONFLICT; MySQL uses ON DUPLICATE KEY UPDATE.
-- PostgreSQL / SQLite
INSERT INTO products (id, name, price)
VALUES (2, 'Monitor 27"', 199.00)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price;
-- MySQL
INSERT INTO products (id, name, price)
VALUES (2, 'Monitor 27"', 199.00)
ON DUPLICATE KEY UPDATE
name = VALUES(name), price = VALUES(price);How do I update a table from another table?
Join the target to a source and set columns from it. Syntax differs a little by vendor; the PostgreSQL UPDATE ... FROM form is shown.
-- Snapshot each customer's lifetime value onto customers
UPDATE customers c
SET lifetime_value = t.lv
FROM (
SELECT customer_id, SUM(total) AS lv
FROM orders GROUP BY customer_id
) t
WHERE t.customer_id = c.id;How do I update rows conditionally with CASE?
A CASE expression inside SET lets one statement apply different values to different rows, avoiding several separate updates.
-- Re-band statuses in one pass
UPDATE orders
SET status = CASE
WHEN total >= 1000 THEN 'priority'
WHEN total >= 100 THEN 'standard'
ELSE 'small'
END
WHERE status = 'paid';How do I delete rows matching a subquery?
Delete based on a related table by filtering on a subquery. Here we remove order items belonging to cancelled orders.
DELETE FROM order_items
WHERE order_id IN (
SELECT id FROM orders WHERE status = 'cancelled'
);How do I delete duplicate rows?
Number the duplicates with ROW_NUMBER keyed on the columns that define "same", then delete everything past the first.
WITH dupes AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY id
) AS rn
FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM dupes WHERE rn > 1);Always preview a delete: run the inner SELECT on its own first, wrap the whole thing in a transaction, and only COMMIT once the row count matches what you expect.
9. Reporting patterns
The queries that end up on dashboards: turning rows into columns, showing each slice as a share of the whole, finding each customer's first order, and detecting runs of consecutive values. These combine the building blocks from the earlier sections - grouping, joins, CASE, and window functions - into the shapes analysts ask for again and again, so they are worth memorising as whole templates rather than reconstructing each time.
How do I pivot rows into columns?
Conditional aggregation is the portable pivot: one SUM(CASE ...) per output column. Here monthly revenue becomes one column per status.
SELECT DATE_TRUNC('month', ordered_at) AS mth,
SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped,
SUM(CASE WHEN status = 'cancelled' THEN total ELSE 0 END) AS cancelled
FROM orders
GROUP BY 1
ORDER BY mth;How do I show each group as a percent of the total?
Divide the group sum by a windowed total over all rows. Multiply by 100.0 to force decimal division.
SELECT category,
SUM(price) AS catalogue_value,
ROUND(
100.0 * SUM(price) / SUM(SUM(price)) OVER (), 1
) AS pct_of_total
FROM products
GROUP BY category
ORDER BY pct_of_total DESC;How do I get a cumulative distribution (percentile bucket)?
NTILE splits ordered rows into equal buckets - handy for quartiles or deciles of customer value.
WITH lv AS (
SELECT customer_id, SUM(total) AS value
FROM orders GROUP BY customer_id
)
SELECT customer_id, value,
NTILE(4) OVER (ORDER BY value DESC) AS quartile
FROM lv;How do I find each customer's first order (cohort)?
Grab the earliest order per customer, then group by its month to size each signup cohort.
WITH firsts AS (
SELECT customer_id,
MIN(ordered_at) AS first_order
FROM orders
GROUP BY customer_id
)
SELECT DATE_TRUNC('month', first_order) AS cohort,
COUNT(*) AS new_customers
FROM firsts
GROUP BY 1
ORDER BY cohort;How do I find gaps and islands?
The classic trick: subtract a ROW_NUMBER from the value. Consecutive runs share the same difference, so you can group by it to collapse each "island".
-- Group consecutive active days into ranges per customer
WITH marked AS (
SELECT customer_id, activity_date,
activity_date - (ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY activity_date
) * INTERVAL '1 day') AS grp
FROM activity
)
SELECT customer_id,
MIN(activity_date) AS streak_start,
MAX(activity_date) AS streak_end,
COUNT(*) AS days
FROM marked
GROUP BY customer_id, grp
ORDER BY customer_id, streak_start;10. Date & time
Date filtering trips people up because functions differ by vendor and because wrapping a column in a function usually kills the index. As a rule, keep the stored column untouched on the left of the comparison and put the computed boundary on the right - write ordered_at >= start, not DATE(ordered_at) = day. Prefer boundary comparisons on the raw column, as shown below. See advanced SQL functions for the full function set.
How do I get rows from the current month?
Compare against the start of the month and the start of the next month, both derived from the current date.
-- PostgreSQL
SELECT id, ordered_at
FROM orders
WHERE ordered_at >= DATE_TRUNC('month', CURRENT_DATE)
AND ordered_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';How do I get the last 7 days?
Subtract an interval from today. Keep the comparison on the bare column so an index can be used.
-- PostgreSQL
SELECT id, ordered_at
FROM orders
WHERE ordered_at >= CURRENT_DATE - INTERVAL '7 days';
-- MySQL
SELECT id, ordered_at
FROM orders
WHERE ordered_at >= CURDATE() - INTERVAL 7 DAY;How do I group sales by month?
Truncate the timestamp to month, then group. Use DATE_TRUNC in PostgreSQL or DATE_FORMAT in MySQL.
-- PostgreSQL
SELECT DATE_TRUNC('month', ordered_at) AS mth,
SUM(total) AS revenue
FROM orders
GROUP BY 1
ORDER BY mth;
-- MySQL: group by 'YYYY-MM'
SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS mth,
SUM(total) AS revenue
FROM orders GROUP BY mth ORDER BY mth;How do I extract parts of a date?
EXTRACT pulls a field (year, month, day of week) out of a date or timestamp for filtering or grouping.
SELECT EXTRACT(YEAR FROM ordered_at) AS yr,
EXTRACT(MONTH FROM ordered_at) AS mo,
COUNT(*) AS orders
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;How do I count orders by day of week?
Extract the weekday number and group by it to see which days are busiest. The exact function name varies, but EXTRACT(DOW ...) works in PostgreSQL (0 = Sunday).
SELECT EXTRACT(DOW FROM ordered_at) AS weekday,
COUNT(*) AS orders
FROM orders
GROUP BY weekday
ORDER BY weekday;How do I calculate age or tenure in years?
Take the difference between two dates. PostgreSQL has AGE; elsewhere compute whole years from the day difference.
-- Employee tenure in whole years (PostgreSQL)
SELECT name,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years
FROM employees
ORDER BY years DESC;
-- MySQL
SELECT name,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years
FROM employees ORDER BY years DESC;Keep going: the fastest way to make these patterns stick is to run them against your own data. Work through the SQL beginner course to build the fundamentals, then the SQL advanced course for window functions, CTEs, and performance.
That is more than fifty of the queries you reach for most often, spanning filtering, sorting, grouping, joins, subqueries, windows, data changes, reporting, and dates. When one does not fit your exact case, the shape almost always does - change the columns, tables, and filters and the structure carries straight over. The real skill is not memorising every query but recognising which of these shapes a new problem maps onto, and that pattern recognition comes fastest from running the examples yourself and tweaking them until they break. For quick syntax lookups keep the cheat sheet handy, and for anything involving OVER (...) the window functions guide goes far deeper than the section above.