davidcrawford · December 19, 2015 10:09
diff --git a/1-example_query_1.sql b/1-example_query_1.sql
 SELECT * FROM purchases ORDER BY created_at DESC;
diff --git a/2-example_query_1_results b/2-example_query_1_results
  id  | customer_id | subtotal | shipping |   tax   |  total   |         created_at         
 ------+-------------+----------+----------+---------+----------+----------------------------
 1101 |       83922 |   102.44 |     4.99 |  8.7074 | 116.1374 | 2013-07-05 22:49:09.20922
 1051 |       83922 |    51.75 |     4.99 | 4.39875 | 61.13875 | 2013-07-05 22:48:45.353301
 1079 |       48582 |    47.02 |     4.99 |  3.9967 |  56.0067 | 2013-07-05 22:48:02.866348
 1057 |       48582 |    30.64 |     4.99 |     2.6 |    38.23 | 2013-07-05 22:47:12.535409
 1048 |       48582 |    81.72 |     4.99 |    6.95 |    93.66 | 2013-07-05 22:46:18.304818
diff --git a/3-example_query_2.sql b/3-example_query_2.sql
 SELECT MAX(created_at) AS most_recent FROM purchases GROUP BY customer_id;
diff --git a/4-example_query_2_results b/4-example_query_2_results
        most_recent         
 ----------------------------
 2013-07-05 22:48:02.866348
 2013-07-05 22:49:09.20922
diff --git a/5-row_number_query.sql b/5-row_number_query.sql
 SELECT *, 
  ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY created_at DESC
  )
  AS created_order
 FROM purchases
diff --git a/6-row_number_results b/6-row_number_results
  id  | customer_id | subtotal | shipping |   tax   |  total   |         created_at         | created_order 
 ------+-------------+----------+----------+---------+----------+----------------------------+---------------
 1079 |       48582 |    47.02 |     4.99 |  3.9967 |  56.0067 | 2013-07-05 22:48:02.866348 |             1
 1057 |       48582 |    30.64 |     4.99 |     2.6 |    38.23 | 2013-07-05 22:47:12.535409 |             2
 1048 |       48582 |    81.72 |     4.99 |    6.95 |    93.66 | 2013-07-05 22:46:18.304818 |             3
 1101 |       83922 |   102.44 |     4.99 |  8.7074 | 116.1374 | 2013-07-05 22:49:09.20922  |             1
 1051 |       83922 |    51.75 |     4.99 | 4.39875 | 61.13875 | 2013-07-05 22:48:45.353301 |             2
diff --git a/7-final_query.sql b/7-final_query.sql
 WITH ordered_purchases AS (
  SELECT *, 
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY created_at DESC
    )
    AS created_order
  FROM purchases
 )
 SELECT * FROM ordered_purchases WHERE created_order = 1;
diff --git a/7-final_query_results b/7-final_query_results
  id  | customer_id | subtotal | shipping |  tax   |  total   |         created_at         | created_order 
 ------+-------------+----------+----------+--------+----------+----------------------------+---------------
 1079 |       48582 |    47.02 |     4.99 | 3.9967 |  56.0067 | 2013-07-05 22:48:02.866348 |             1
 1101 |       83922 |   102.44 |     4.99 | 8.7074 | 116.1374 | 2013-07-05 22:49:09.20922  |             1
	id \| customer_id \| subtotal \| shipping \| tax \| total \| created_at
	------+-------------+----------+----------+---------+----------+----------------------------
	1101 \| 83922 \| 102.44 \| 4.99 \| 8.7074 \| 116.1374 \| 2013-07-05 22:49:09.20922
	1051 \| 83922 \| 51.75 \| 4.99 \| 4.39875 \| 61.13875 \| 2013-07-05 22:48:45.353301
	1079 \| 48582 \| 47.02 \| 4.99 \| 3.9967 \| 56.0067 \| 2013-07-05 22:48:02.866348
	1057 \| 48582 \| 30.64 \| 4.99 \| 2.6 \| 38.23 \| 2013-07-05 22:47:12.535409
	1048 \| 48582 \| 81.72 \| 4.99 \| 6.95 \| 93.66 \| 2013-07-05 22:46:18.304818
	most_recent
	----------------------------
	2013-07-05 22:48:02.866348
	2013-07-05 22:49:09.20922
	SELECT *,
	ROW_NUMBER() OVER (
	PARTITION BY customer_id ORDER BY created_at DESC
	)
	AS created_order
	FROM purchases
	WITH ordered_purchases AS (
	SELECT *,
	ROW_NUMBER() OVER (
	PARTITION BY customer_id ORDER BY created_at DESC
	)
	AS created_order
	FROM purchases
	)
	SELECT * FROM ordered_purchases WHERE created_order = 1;