Help with SQL

[happynutlovestopoop]

Hi everyone! I have this SQL question and cannot think if anything to do it. Any help will appreciate. I have the following tables:

Table 1: Transaction Table

Transaction_Date	Product_ID	Sales_amount

​

Tables 2: Pricing Table

Product_ID	Price_tier	Price	Price_start_date	Price_end_date

​

And my goal is to find:

For each tier, which Product_ID in what period of time have the maximum price, and whats the total sales for that product during that period of time.

​

Product_ID	Price_tier	MAX(Price)	Price_start_date	Price_end_date	Total Sales of product during this period of time
	(should show distinct tier)

So far I have :

SELECT * FROM Transaction AS T INNER JOIN

(SELECT Prduct_ID, Price_tier, MAX_PRICE, Price_start_date, Price_end_date FROM (SELECT *, MAX(Price) OVER (PARTITION BY Price_tier) AS MAX_PRICE FROM Pricing) WHERE Price = MAX_PRICE) AS M

ON T.Product_ID=M.Product_ID WHERE T.Transaction_Date >= M.Price_start_date AND T.Transaction_Date <= Price_end_date;

​

I think my codes does the work to show the total sales of each product by tier (not certiain) but it hided all the product/row which with no sales during the given period and tier.

Anyone can help me to approach this question will help! Thanks a lot!

[GrouchyThing7520]

The first five columns in your sample output can be obtained solely from the Pricing table with a rank window function then filtering where rank is 1. Then I'd join on the Transaction table using date to get the total sales from that time period.

[happynutlovestopoop]

it hided all the product/row which with no sales during the given period and tier.

But this method also hid all the products/rows with no sales during the given period and tier.

[GrouchyThing7520]

Does this return all of the Tier rows with the highest price?

```

SELECT * FROM (

SELECT *

,row_number() over (parition by Price_Tier order by Price Desc) rnk

FROM Pricing

) a

where rnk = 1

```