Solution for 1084. Sales Analysis III
Intuition\r\nObtain all the product id that were sold apart from requested dates.\r\noutput unique pid, pnames that are not in above result\r\n\r\n# Approach\r\
# Intuition\r\nObtain all the product id that were sold apart from requested dates.\r\noutput unique pid, pnames that are not in above result\r\n\r\n# Approach\r\nUse temp table to join both sales and pproduct table\r\nInner join is needed.\r\n\r\n# Complexity\r\n- Time complexity:\r\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\r\n\r\n- Space complexity:\r\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\r\n\r\n# Code\r\n```\r\n# Write your MySQL query statement below\r\n/* creating temp table*/\r\nWITH new_tbl AS\r\n\r\n(SELECT a.product_id AS pid,a.product_name AS pname, b.sale_date AS sd\r\nFROM Product AS a\r\nINNER JOIN Sales AS b\r\nON a.product_id = b.product_id)\r\n\r\n/*main query*/\r\nSELECT pid AS product_id,pname AS product_name\r\nFROM new_tbl\r\nWHERE pid NOT IN \r\n/* sub query to identify all the product id that were sold apart from requested dates.*/\r\n(\r\nSELECT pid FROM new_tbl \r\nWHERE sd NOT BETWEEN \'2019-01-01\' AND \'2019-03-31\'\r\n)\r\n\r\n/* use group by to obtain unique values */\r\nGROUP BY pid\r\n;\r\n```
['MySQL']