SQL query to obtain average price on most recent invoices by company and product

Question

Table is set up as below:

I'm trying to get an average of the last 7 invoices by product code and by company.

Company	Product_Code	Price	Invoice Date
A	XYZ	10	01012023
B	XYZ	11	01012023
C	XYZ	12	01012023
A	ABC	12	01022023
B	ABC	13	01022023
C	ABC	14	01022023
A	LMN	15	01022023

I tried something like:

SELECT 
    t1.company, t1.product_code, AVG(price) 
FROM 
   (SELECT 
        * 
    FROM
        Table
    ORDER BY invoice_date DESC 
    LIMIT 7) AS t1
GROUP BY t1.company, t1.product_code, t1.price
HAVING t1.company = 'A' or t1.company = 'B' or t1.company = 'C'

But this is just returning the last 7 invoices from the table-- how can I get the last 7 invoices by company and product code?

Answer 1

This will do it for the last 7 by Invoice Date for all rows:

SELECT company, product_code, AVG(price) 
FROM a
WHERE "Invoice Date" IN (SELECT TOP 7 "Invoice Date" FROM a ORDER BY "Invoice Date" DESC)
GROUP BY company, product_code

This will do it for last 7 for each company/product code combo:

SELECT a.company, a.product_code, AVG(a.price) FROM a
JOIN (SELECT company, product_code, MIN("Invoice Date") "Invoice Date" FROM a WHERE "Invoice Date" IN (SELECT TOP 7 "Invoice Date" FROM a ORDER BY "Invoice Date" DESC) GROUP BY company, product_code) b
    ON a.company = b.company AND a.product_code = b.product_code AND a."Invoice Date" = b."Invoice Date"
GROUP BY a.company, a.product_code