Subquery Examples

Inline Subquery

creates a table that you could then query again in the FROM statement

Expert Tip

Note that you should not include an alias when you write a subquery in a conditional statement. This is because the subquery is treated as an individual value (or set of values in the IN case) rather than as a table. Nested and Scalar subqueries often do not require aliases the way WITH and Inline subqueries do.

SELECT *
FROM orders
WHERE DATE_TRUNC('month',occurred_at) =
 (SELECT DATE_TRUNC('month',MIN(occurred_at)) AS min_month
  FROM orders)
ORDER BY occurred_at

This query works because the result of the subquery is only one cell. Most conditional logic will work with subqueries containing one-cell results. But IN is the only type of conditional logic that will work when the inner query contains multiple results.

id	account_id	occurred_at	standard_qty	gloss_qty	poster_qty	total	standard_amt_usd	gloss_amt_usd	poster_amt_usd	total_amt_usd
5786	2861	2013-12-04T04:22:44.000Z	0	48	33	81	0.00	359.52	267.96	627.48
2415	2861	2013-12-04T04:45:54.000Z	490	15	11	516	2445.10	112.35	89.32	2646.77
4108	4311	2013-12-04T04:53:25.000Z	528	10	0	538	2634.72	74.90	0.00	2709.62
4489	1281	2013-12-05T20:29:16.000Z	0	37	0	37	0.00	277.13	0.00	277.13
287	1281	2013-12-05T20:33:56.000Z	492	73	0	565	2455.08	546.77	0.00	3001.85
1946	2481	2013-12-06T02:13:20.000Z	502	4	33	539	2504.98	29.96	267.96	2802.90
6197	3431	2013-12-06T12:55:22.000Z	53	559	315

STEP 1 – Use DATE_TRUNC to pull month level information above the first order ever placed in the orders table.

SELECT DATE_TRUNC('month', MIN(occurred_at)) as first_order
FROM orders;

first_order
2013-12-01T00:00:00.000Z

STEP 2 – Use the result of the previous query to find only the orders that took place in the same month and year as the first order, and then pull the average for each type of paper qty in this month.

Find all the orders placed on the date from first_order
Get the average qty of all those orders placed per paper type
Final output – column per each paper type with average qty

SELECT AVG(standard_qty) AVG_Standard, AVG(gloss_qty) AVG_Gloss, AVG(poster_qty) AVG_Poster
FROM orders
	WHERE DATE_TRUNC('month', occurred_at) = (SELECT 	DATE_TRUNC('month', MIN(occurred_at)) as first_order
	FROM orders)

avg_standard	avg_gloss	avg_poster
268.2222222222222222	208.9494949494949495	111.8181818181818182

The total amount spent on all orders on the first month that any order was placed in the orders table (in terms of usd).

SELECT SUM(total_amt_usd)
FROM orders
	WHERE DATE_TRUNC('month', occurred_at) = (SELECT 	DATE_TRUNC('month', MIN(occurred_at)) as first_order
	FROM orders)

sum
377331.00

I got everything correct! 😊

rose learns.to('code');