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.

idaccount_idoccurred_atstandard_qtygloss_qtyposter_qtytotalstandard_amt_usdgloss_amt_usdposter_amt_usdtotal_amt_usd
578628612013-12-04T04:22:44.000Z04833810.00359.52267.96627.48
241528612013-12-04T04:45:54.000Z49015115162445.10112.3589.322646.77
410843112013-12-04T04:53:25.000Z5281005382634.7274.900.002709.62
448912812013-12-05T20:29:16.000Z0370370.00277.130.00277.13
28712812013-12-05T20:33:56.000Z4927305652455.08546.770.003001.85
194624812013-12-06T02:13:20.000Z5024335392504.9829.96267.962802.90
619734312013-12-06T12:55:22.000Z53559315

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_standardavg_glossavg_poster
268.2222222222222222208.9494949494949495111.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! 😊 

Leave a Reply