-------------- select * from store_revenue -------------- +----+---------------------+----------+------------------+---------+ | id | date | brand_id | store_location | revenue |...

there are 2 questions I Already started question 1 but I have to merge the the GEO1 and geo column as one column . LOOK. AT THE MERGED_TABLE COLUMN


-------------- select * from store_revenue -------------- +----+---------------------+----------+------------------+---------+ | id | date | brand_id | store_location | revenue | +----+---------------------+----------+------------------+---------+ | 1 | 2016-01-01 00:00:00 | 1 | United States-CA | 100 | | 2 | 2016-01-01 00:00:00 | 1 | United States-TX | 420 | | 3 | 2016-01-01 00:00:00 | 1 | United States-NY | 142 | | 4 | 2016-01-02 00:00:00 | 1 | United States-CA | 231 | | 5 | 2016-01-02 00:00:00 | 1 | United States-TX | 2342 | | 6 | 2016-01-02 00:00:00 | 1 | United States-NY | 232 | | 7 | 2016-01-03 00:00:00 | 1 | United States-CA | 100 | | 8 | 2016-01-03 00:00:00 | 1 | United States-TX | 420 | | 9 | 2016-01-03 00:00:00 | 1 | United States-NY | 3245 | | 10 | 2016-01-04 00:00:00 | 1 | United States-CA | 34 | | 11 | 2016-01-04 00:00:00 | 1 | United States-TX | 3 | | 12 | 2016-01-04 00:00:00 | 1 | United States-NY | 54 | | 13 | 2016-01-05 00:00:00 | 1 | United States-CA | 45 | | 14 | 2016-01-05 00:00:00 | 1 | United States-TX | 423 | | 15 | 2016-01-05 00:00:00 | 1 | United States-NY | 234 | | 16 | 2016-01-01 00:00:00 | 2 | United States-CA | 234 | | 17 | 2016-01-01 00:00:00 | 2 | United States-TX | 234 | | 18 | 2016-01-01 00:00:00 | 2 | United States-NY | 142 | | 19 | 2016-01-02 00:00:00 | 2 | United States-CA | 234 | | 20 | 2016-01-02 00:00:00 | 2 | United States-TX | 3423 | | 21 | 2016-01-02 00:00:00 | 2 | United States-NY | 2342 | | 22 | 2016-01-03 00:00:00 | 2 | United States-CA | 234234 | | 23 | 2016-01-06 00:00:00 | 3 | United States-TX | 3 | | 24 | 2016-01-03 00:00:00 | 2 | United States-TX | 3 | | 25 | 2016-01-03 00:00:00 | 2 | United States-NY | 234 | | 26 | 2016-01-04 00:00:00 | 2 | United States-CA | 2 | | 27 | 2016-01-04 00:00:00 | 2 | United States-TX | 2354 | | 28 | 2016-01-04 00:00:00 | 2 | United States-NY | 45235 | | 29 | 2016-01-05 00:00:00 | 2 | United States-CA | 23 | | 30 | 2016-01-05 00:00:00 | 2 | United States-TX | 4 | | 31 | 2016-01-05 00:00:00 | 2 | United States-NY | 124 | +----+---------------------+----------+------------------+---------+ 31 rows in set (0.00 sec) -------------- select *from marketing_data -------------- +----+---------------------+------+-------------+--------+ | id | date | geo | impressions | clicks | +----+---------------------+------+-------------+--------+ | 1 | 2016-01-01 00:00:00 | TX | 2532 | 45 | | 2 | 2016-01-01 00:00:00 | CA | 3425 | 63 | | 3 | 2016-01-01 00:00:00 | NY | 3532 | 25 | | 4 | 2016-01-01 00:00:00 | MN | 1342 | 784 | | 5 | 2016-01-02 00:00:00 | TX | 3643 | 23 | | 6 | 2016-01-02 00:00:00 | CA | 1354 | 53 | | 7 | 2016-01-02 00:00:00 | NY | 4643 | 85 | | 8 | 2016-01-02 00:00:00 | MN | 2366 | 85 | | 9 | 2016-01-03 00:00:00 | TX | 2353 | 57 | | 10 | 2016-01-03 00:00:00 | CA | 5258 | 36 | | 11 | 2016-01-03 00:00:00 | NY | 4735 | 63 | | 12 | 2016-01-03 00:00:00 | MN | 5783 | 87 | | 13 | 2016-01-04 00:00:00 | TX | 5783 | 47 | | 14 | 2016-01-04 00:00:00 | CA | 7854 | 85 | | 15 | 2016-01-04 00:00:00 | NY | 4754 | 36 | | 16 | 2016-01-04 00:00:00 | MN | 9345 | 24 | | 17 | 2016-01-05 00:00:00 | TX | 2535 | 63 | | 18 | 2016-01-05 00:00:00 | CA | 4678 | 73 | | 19 | 2016-01-05 00:00:00 | NY | 2364 | 33 | | 20 | 2016-01-05 00:00:00 | MN | 3452 | 25 | +----+---------------------+------+-------------+--------+ 20 rows in set (0.00 sec) Question #3 * Merge these two datasets so we can see impressions, clicks, and revenue together by date and geo. SELECT * FROM marketing_data ORDER BY date; CREATE TABLE store_revenue2 AS SELECT id,SUBSTRING(store_location, 15,16) AS GEO1,revenue,date AS date1 FROM store_revenue; SELECT * FROM store_revenue2 ORDER BY date1; CREATE TABLE MERGE_TABLE AS SELECT s.GEO1,s.revenue,m.clicks,m.impressions,m.geo,CASE WHEN m.date= s.date1 THEN s.date1 ELSE s.date1 END AS COMBINED_DATE FROM (SELECT GEO1,revenue,date1 , ROW_NUMBER() OVER (ORDER BY GEO1) as pn FROM store_revenue2) AS s LEFT JOIN (SELECT clicks,impressions,geo,date,ROW_NUMBER() OVER (ORDER BY geo) as pn FROM marketing_data) AS m ON s.pn=m.pn; SELECT * FROM MERGE_TABLE;