CODEWITHSUNDEEP
sqlsql-servert-sql
Nebelz Cheez
Nebelz Cheez

Reputation: 307

Joining 3 tables with Conditional Aggregation

I am figuring out how to find Order extensions rate for a user for the data below. All orders are in Order table and all Orderitems are stored in RentalItems. An order can contain 1 or more Items. If a User Extends Any 1 Item, this means the order is Extended. So I want to find out the Order Extension Rate of a user by checking Total OrderExtensionsByUser/TotalOrdersPlacedByUser

User Table: UserID, fname, LName, Position,

Order Table: User 7 placed 2 orders and User 3 placed 3 Orders

OrderID UserID
1 7
2 3
3 7
4 3
5 3

RentalItems:

RentalItemID OrderID ItemID
1 1 3
2 1 4
3 2 5
4 2 6
5 2 9
6 3 7
7 4 11
8 5 12

RentalExtions: Both Order 1&2 have been extended since one more or more than 1 items in that order have been extended

RentalItemID ExtensionBy_UserID ExtDate
1 7 2020-07-20
2 7 2020-03-05
3 3 2020-03-05
8 3 2020-03-05

So in this case, For user 7 and 3, it should be,

TotalOrderExtensionsByUser/AllOrdersPlacedByUser:

  1. userID 7: 1/2= 0.50
  2. userID 3: 2/3= 0.66

Desired output ( for all users, not for particular user )

userID fname Lname ExtRate
7 jon smith 0.50
3 smith tucker 0.66

Any possible way to do this?

The code I had working before, Which was also given by a user on stackOverflow, was below. But I realized it only checks for extension rate by user within ExtensionTable. But in reality, what I am looking for is different.

This code will look within RentalExtensions and give me output as 2/4=0.40 for user 3. But what I need is different AS I Explained Above.


select UserID, fname,  lname, JobTitle,
    (select avg(case when re.ExtensionBy_UserID = r.userId then 1.0 else 0 end)
      from RentalExtensions re
      cross apply (values (userId) ) r(userId)
     ) as ExtensionRate from [user]
where userID in (select distinct ExtensionBy_UserID from RentalExtensions) and JobTitle = 'Support Staff'

Edit 1:

Upvotes: 0

Views: 76

Answers (1)

Dale K
Dale K

Reputation: 27201

I think this might work... if you provide the DDL+DML I'll test it for you.

select U.UserId, convert(decimal(9,2),sum(HasExtension) / count(*))
from (
  select OrderId, UserId
    -- To ensure the sum is decimal not int
    , convert(decimal(9,2),case when exists (select 1 from RentalExtions E where E.RentalItemId in (select RI.RentalItemId from RentalItem RI where RI.OrderId = O.OrderId)) then 1 else 0 end) HasExtension
  from [Order] O
) O
inner join [User] U on U.UserId = O.UserId
group by U.UserId;

I strongly encourage you to provide sample data as DDL+DML in all future questions as that makes it much easier for us to answer.

Upvotes: 2

Related Questions