vmart_query_08.sql
-- vmart_query_08.sql -- Using pre-join projections to answer subqueries -- between online_sales_fact and online_page_dimension
SELECT page_description, page_type, start_date, end_date FROM online_sales.online_sales_fact f, online_sales.online_page_dimension d WHERE f.online_page_key = d.online_page_key AND page_number IN (SELECT MAX(page_number) FROM online_sales.online_page_dimension) AND page_type = 'monthly' AND start_date = '2012-06-02';
Output
page_description | page_type | start_date | end_date ---------------------------+-----------+------------+----------- Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11 (12 rows)