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

Question

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

question from:https://stackoverflow.com/questions/65924237/how-to-calculate-the-ratio-of-records-based-on-values-of-two-columns-distinctly

Answer 1

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.