1

I have the following schema:

enter image description here

From this, I would like for each market to find the names of the product that has brought the company the most cumulative sales (as measured by InvoicePrice).

I tried the following in mysql :

create table Contacts (
    CustomerID integer primary key,
    FullName text,
    MarketID text
);

create table Order_ (
    OrderID integer primary key,
    CustomerID integer,
    OrderDate date,
    foreign key (CustomerID) references Contacts(CustomerID)
);

create table Product (
    ProductCode integer primary key,
    Brand text,
    ProductName text
);

create table OrderedItems (
    OrderedItemID integer primary key,
    OrderID integer not NULL,
    ProductCode integer not NULL,
    InvoicePrice integer,
    foreign key (ProductCode) references Product(ProductCode),
    foreign key (OrderID) references Order_(OrderID)
);

insert into Contacts values(1, "John", "MarketA");
insert into Contacts values(2, "Jack", "MarketA");
insert into Contacts values(3, "George", "MarketB");
insert into Contacts values(4, "Karen", "MarketC");
insert into Contacts values(5, "Lucy", "MarketB");

insert into Product values(1, "Western Digital", "Hard drive 1TB");
insert into Product values(2, "Samsung", "Monitor 27");
insert into Product values(3, "nVidia", "GPU 8GB");

insert into Order_ values(1, 1, "2020-1-1"); -- John's
insert into Order_ values(2, 1, "2020-1-2"); -- John's
insert into Order_ values(3, 1, "2021-1-2"); -- John's
insert into Order_ values(4, 5, "2020-1-1"); -- Lucy's
insert into Order_ values(5, 4, "2020-1-1"); -- Karen's

insert into OrderedItems values(1, 1, 1, 1000); -- John's 1st order, 1st item
insert into OrderedItems values(2, 1, 1, 1000); -- John's 1st order, 2nd item
insert into OrderedItems values(3, 2, 2, 300); -- John's 2nd order, 1st item
insert into OrderedItems values(4, 4, 1, 1000); -- Lucy's 1st order, 1st item

select Contacts.MarketID, Y.ProductName, sum(Y.Price) as TotalSales
    from (
        select T.ProductName as ProductName, T.Price as Price, Order_.CustomerID as CustomerID 
            from (
                select Product.ProductName as ProductName, OrderedItems.OrderID as OrderID, OrderedItems.InvoicePrice as Price 
                from Product join OrderedItems on OrderedItems.ProductCode = Product.ProductCode
            ) as T
            join Order_ on Order_.OrderID = T.OrderID
    ) as Y
    join Contacts on Contacts.CustomerID = Y.CustomerID
    group by Contacts.MarketID, Y.ProductName
;

I get this output...

MarketID ProductName TotalSales
MarketA Hard drive 1TB 2000
MarketB Hard drive 1TB 1000
MarketA Monitor 27 300

But I would like to get this output (Since Hard drive 1TB has more cumulative sales in Market A)...

MarketID ProductName TotalSales
MarketA Hard drive 1TB 2000
MarketB Hard drive 1TB 1000
2

1 Answer 1

1

Ranking your TotalSales by Market and then selecting back rows where rank is 1 should help you get what you’re looking for.

select MarketId,
    ProductName,
    TotalSales
from
    (
    select Contacts.MarketID,
        Y.ProductName,
        sum(Y.Price) as TotalSales,
        rank() over ( partition by Contacts.MarketID order by sum(Y.Price) desc ) as "sale_rank"
    from (
        select T.ProductName as ProductName,
            T.Price as Price,
            Order_.CustomerID as CustomerID 
        from (
            select Product.ProductName as ProductName,
                OrderedItems.OrderID as OrderID,
                OrderedItems.InvoicePrice as Price 
            from Product
            join OrderedItems on OrderedItems.ProductCode = Product.ProductCode
            ) as T
        join Order_ on Order_.OrderID = T.OrderID
    ) as Y
    join Contacts on Contacts.CustomerID = Y.CustomerID
    group by Contacts.MarketID,
        Y.ProductName
) as s
where s.sale_rank = 1
MarketId ProductName TotalSales
MarketA Hard drive 1TB 2000
MarketB Hard drive 1TB 1000

Fiddle

2
  • is it possible to add another question or should I create a new post?
    – alan watt
    Feb 13 at 14:46
  • If you’re seeking additional clarity on the answer, feel free to ask it here, and I’ll update the answer to cover it. If it’s unrelated to the original question/answer, I’d suggest you start a new post. Feb 13 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.