How to calculate the ratio of records based on values of two columns distinctly with the inner join of another table?

user_15093191 0
Viewed 121

I'm developing a new analyst feature for an internal tool my company will (hopefully if I do well) use.

For simplicity sake, let's say we have

CREATE TABLE Products (
    ProductID varchar,
    Description varchar,
   ....
);

and

CREATE TABLE Orders (
    ProductID varchar,
    Bought date,
    Returned date,
   ....
);

The tables would look something like this:

Products

ProductID Description
SPO00 Sports product 1
SPO01 Sports product 2
SPO02 Sports product 3
ELE00 Electronics product 1
ELE02 Electronics product 2

Orders

ProductID Bought Returned
ELE00 2021-01-05 2021-01-07
SPO00 2021-01-01 NULL
SPO00 2021-01-05 2021-01-08
SPO00 2021-01-08 NULL
SPO01 2021-01-10 NULL
SPO01 2021-01-15 NULL
SPO02 2021-01-18 2021-01-20

I'd like to make a request to our DB and retrieve the description of specific products, and the percentage of bought products that are eventually returned.

I'd would also like to add specific parameters to the query, for example select only orders from beginning of the year as well as only the products from a specific department, for example.

So, it would looks something like this:

Description ratio returned
Sports product 1 0.33
Sports product 2 0.00
Sports product 3 1.0

So, the products table might have product lines of electronics and sports and ProductID would be ELE00-ELE05 and SPO00-SPO03, respectively.

The above table is grabbing all products that have ProductID with SPO prefix and getting that specific products bought and returned ratio.

I've only been able to get the specific products, but the returned ratio is the same for each row. I think because its not doing the ratio calculation for each distinct product. I think its doing one overall ratio calculation and displaying that for each product.

Here is the query I've tried.

SELECT DISTINCT Product.Description, (CAST((SELECT DISTINCT COUNT(*) FROM Orders WHERE(ProductID like 'SPO%' AND Returned > '2021-01-01') AS FLOAT)) / (CAST((SELECT DISTINCT COUNT(*) FROM Orders WHERE (ProductID like 'SPO%' AND Bought > '2021-01-01') AS FLOAT)) AS returnedRatio
FROM Product INNER JOIN
Orders ON Orders.ProductID = Product.ProductID

I'm thinking I might need to do a nested query to get the ratios for each product and then get the description?

All help would be greatly appreciated because I've never done very complex queries so I'm still learning.

ssms entity-framework sql
1 Answers

Does this work for you?

I used a case expression inside the count() function to count the number of returned products.

The * 1.0 turns the integer division into a decimal division without explicitly casting.

Sample data

CREATE TABLE Products (
    ProductID nvarchar(5),
    Description nvarchar(50)
);

insert into Products (ProductId, Description) values
('SPO00',   'Sports product 1'),
('SPO01',   'Sports product 2'),
('SPO02',   'Sports product 3'),
('ELE00',   'Electronics product 1'),
('ELE02',   'Electronics product 2');

CREATE TABLE Orders (
    ProductID nvarchar(5),
    Bought date,
    Returned date
);

insert into Orders (ProductID, Bought, Returned) values
('ELE00', '2021-01-05', '2021-01-07'),
('SPO00', '2021-01-01', NULL),
('SPO00', '2021-01-05', '2021-01-08'),
('SPO00', '2021-01-08', NULL),
('SPO01', '2021-01-10', NULL),
('SPO01', '2021-01-15', NULL),
('SPO02', '2021-01-18', '2021-01-20');

Solution

select p.Description,
       count(case when o.Returned is not null then 1 end) as ReturnCount,
       count(1) TotalCount,
       count(case when o.Returned is not null then 1 end) * 1.0 / count(1) as ReturnRatio
from Products p
join Orders o
  on o.ProductID = p.ProductID
where p.ProductID like 'SPO%'
  and o.Bought >= '2021-01-01'
group by p.Description;

Result

Description       ReturnCount  TotalCount  ReturnRatio
----------------  -----------  ----------  --------------
Sports product 1  1            3           0.333333333333
Sports product 2  0            2           0
Sports product 3  1            1           1

Fiddle to see things in action.

user_16079510
Related