Easiest and Fastest | Complete Beginner Guide 👍
There are two tricks that might make this query a bit difficult: \n1. How do we join the tables together when we have start and end dates on one and just a purc
There are two tricks that might make this query a bit difficult: \n1. _How do we join the tables together when we have start and end dates on one and just a purchase date on the other?_\n2. _The calculation of the average is not straight forward..._\n\nLet\'s tackle these one-by-one.\n\n__1. So how do we join this data together?__ The trick here is that __you can use any logical operator you want to join tables__. We\'re used to seeing joins done like this `JOIN t2 ON t2.column = t1.column`, but we are not limited to only using `=` for join logic. We could just as easily do `ON t2.column LIKE t1.column`, or use `AND` and `OR` to combine logic for more complex joins. In fact, __any logic that you can use in a `WHERE` clause can be used in your `JOIN` clauses__, and more complicated joins can read very much like a `WHERE` clause.\n\nFor this problem, we want to join these tables where product_id values are equal __AND where the unit purchase date is BETWEEN the start_date and end_date values of the `prices` table__. So this join will be tricky, in that we are joining based on two criteria (product_id and dates) and that we are using `BETWEEN` as the logical operator to join based on the dates. So this is what our join will look like:\n```\nFROM unitssold u \nJOIN prices p ON 1=1\n    AND p.product_id = u.product_id \n    AND u.purchase_date BETWEEN p.start_date AND p.end_date\n```\n_Note: you might wonder why that "`ON 1=1`" is there. It doesn\'t actually do anything at all; it is just a trick that allows us to separate the real logical steps of our join onto separate lines for readability\'s sake. Since `1=1` is always True, then `1=1 AND other_condition` will only depend on `other_condition`, so we can add it to the join without it affecting anything. Note on the Note: if you are using `OR` rather than `AND`, then putting `1=0` will have the same effect._\n\n__2. How do we calculate this weird average?__ We\'re provided the following formula to calculate the average price, `Average selling price = Total Price of Product / Number of products sold`. _So how do we get these numbers?_ The denominator is easy enough: just `SUM(unitssold.units)`. The numerator requires a bit of thinking. The "Total Price of Product" would be more accurately named "_Extended Price_ of Product," which is a fancy retail name for... "__Sales__". "Sales" is pretty easy to wrap your head around; it\'s just `SUM(price * units)`. So our average selling price is calculated by `SUM(price * units) / SUM(units)`.\n\nNow, we\'re not quite done. We\'re using the aggregate, or group, function `SUM` here, so we need to ask ourselves _Over what group are we calculating the sum?_ We need the average price __by product__, so we will need to group our query by `product_id`.\n\nNow we have our final query:\n\n```\nSELECT\n    u.product_id,\n    ROUND(SUM(p.price * u.units) / SUM(u.units), 2) AS average_price    -- Note: the prompt requires us to round results to 2 places.\nFROM unitssold u \nJOIN prices p ON 1=1  -- remember, the 1=1 is just so we can format the code better.\n    AND p.product_id = u.product_id \n    AND u.purchase_date BETWEEN p.start_date AND p.end_date  -- we can use any logic to join two tables, not just "="\nGROUP BY u.product_id\n```\n\nHope this helps, and happy coding!
['MySQL']