SQL (MySQL) Query To Extract Aggregates Over Multiple Timeframes

Question

Suppose I have the following table, my_table:

+----------+------------+------------+
+ key_code + cost_value + cost_date  +
+----------+------------+------------+
+   AAAA   +       1.01 + 2015-01-05 +
+   AAAA   +       4.04 + 2015-01-10 +
+   AAAA   +       3.03 + 2015-01-15 +
+   AAAA   +       2.02 + 2015-01-20 +
+   BBBB   +       5.05 + 2015-01-05 +
+   BBBB   +       8.08 + 2015-01-10 +
+   BBBB   +       7.07 + 2015-01-15 +
+   BBBB   +       6.06 + 2015-01-20 +
+----------+------------+------------+

I can extract the minimum and maximum cost_values for each key_code with the following query:

SELECT key_code, MIN(cost_value) AS cost_min, MAX(cost_value) AS cost_max 
FROM my_table 
GROUP BY key_code 
ORDER BY key_code;

+----------+----------+----------+
+ key_code + cost_min + cost_max +
+----------+----------+----------+
+   AAAA   +     1.01 +     4.04 +
+   BBBB   +     5.05 +     8.08 +
+----------+----------+----------+

I can limit the min/max to cost_values from the last 7 days (assume Today = 2015-01-21) with the following query:

SELECT key_code, MIN(cost_value) AS cost_min_07, MAX(cost_value) AS cost_max_07 
FROM my_table 
WHERE cost_date >= (CURDATE() - INTERVAL 7 DAY) 
GROUP BY key_code 
ORDER BY key_code;

+----------+-------------+-------------+
+ key_code + cost_min_07 + cost_max_07 +
+----------+-------------+-------------+
+   AAAA   +        2.02 +        3.03 +
+   BBBB   +        6.06 +        7.07 +
+----------+-------------+-------------+

But what if I want to extract min/max for the previous 7, 14 and 21 days simultaneously? How can I (most efficiently) generate the following result? I guess I'm asking how to apply a different WHERE to each MIN() and MAX() pair?

+----------+-------------+-------------+-------------+-------------+-------------+-------------+
+ key_code + cost_min_07 + cost_max_07 + cost_min_14 + cost_max_14 + cost_min_21 + cost_max_21 +
+----------+-------------+-------------+-------------+-------------+-------------+-------------+
+   AAAA   +        2.02 +        3.03 +        2.02 +        4.04 +        1.01 +        4.04 +
+   BBBB   +        6.06 +        7.07 +        6.06 +        8.08 +        5.05 +        8.08 +
+----------+-------------+-------------+-------------+-------------+-------------+-------------+

Answer 1

Use conditional aggregation:

SELECT key_code,
       MIN(CASE WHEN cost_date >= CURDATE() - INTERVAL  7 DAY THEN cost_value END) AS cost_min_07,           
       MAX(CASE WHEN cost_date >= CURDATE() - INTERVAL  7 DAY THEN cost_value END) AS cost_max_07, 
       MIN(CASE WHEN cost_date >= CURDATE() - INTERVAL 14 DAY THEN cost_value END) AS cost_min_14,           
       MAX(CASE WHEN cost_date >= CURDATE() - INTERVAL 14 DAY THEN cost_value END) AS cost_max_14, 
       MIN(CASE WHEN cost_date >= CURDATE() - INTERVAL 21 DAY THEN cost_value END) AS cost_min_21,           
       MAX(CASE WHEN cost_date >= CURDATE() - INTERVAL 21 DAY THEN cost_value END) AS cost_max_21 
FROM my_table 
WHERE cost_date >= (CURDATE() - INTERVAL 21 DAY) 
GROUP BY key_code 
ORDER BY key_code;