--------------
EXPLAIN select o_year, sum(case when nation = 'UNITED STATES' then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'STANDARD BURNISHED STEEL' ) as all_nations group by o_year order by o_year
--------------

+----+-------------+------------+--------+----------------------------------------------------------------------------------------+---------------+---------+---------------------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys                                                                          | key           | key_len | ref                       | rows | Extra                           |
+----+-------------+------------+--------+----------------------------------------------------------------------------------------+---------------+---------+---------------------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                                   | NULL          | NULL    | NULL                      | 9782 | Using temporary; Using filesort | 
|  2 | DERIVED     | n2         | ALL    | PRIMARY                                                                                | NULL          | NULL    | NULL                      |   25 |                                 | 
|  2 | DERIVED     | supplier   | ref    | PRIMARY,i_s_nationkey                                                                  | i_s_nationkey | 5       | dbt3.n2.n_nationkey       |  594 | Using where; Using index        | 
|  2 | DERIVED     | lineitem   | ref    | PRIMARY,i_l_suppkey,i_l_suppkey_partkey,i_l_orderkey_quantity,i_l_orderkey,i_l_partkey | i_l_suppkey   | 5       | dbt3.supplier.s_suppkey   |  299 | Using where                     | 
|  2 | DERIVED     | part       | eq_ref | PRIMARY                                                                                | PRIMARY       | 4       | dbt3.lineitem.l_partkey   |    1 | Using where                     | 
|  2 | DERIVED     | orders     | eq_ref | PRIMARY,i_o_orderdate,i_o_custkey                                                      | PRIMARY       | 4       | dbt3.lineitem.l_orderkey  |    1 | Using where                     | 
|  2 | DERIVED     | customer   | eq_ref | PRIMARY,i_c_nationkey                                                                  | PRIMARY       | 4       | dbt3.orders.o_custkey     |    1 |                                 | 
|  2 | DERIVED     | n1         | eq_ref | PRIMARY,i_n_regionkey                                                                  | PRIMARY       | 4       | dbt3.customer.c_nationkey |    1 |                                 | 
|  2 | DERIVED     | region     | eq_ref | PRIMARY                                                                                | PRIMARY       | 4       | dbt3.n1.n_regionkey       |    1 | Using where                     | 
+----+-------------+------------+--------+----------------------------------------------------------------------------------------+---------------+---------+---------------------------+------+---------------------------------+