SQL Query to Get Count of Order ,Download app and Sign up in optimize way

user_10188472 0
Viewed 57

My Partner table :

Partner Id Name
1 abc
2 xyz

My Customer table:

Customer Id Name Sign_up_Referral_By Download_Referral_By Order_Referral_By
111 DEW 1
112 ERU 2 2

Note :

Customer table columns Sign_up_Referral_By, Download_Referral_By, Order_Referral_By are foreign keys referencing the Partner table's Partner id column.

I want to get count of number of download, sign up by partner referral Id :

My expected output is

Partner Id Name Sign up Referral By Count Download Referral By count Order Referral By count
1 abc 1
2 xyz 1 1

I have tried this code in SQL Server:

SELECT
    [partner].USER_GUID, [partner].NAME,
    COUNT([customer].SIGN_UP_REFERAL_BY) Sign_Up_Count,
    COUNT([customer].APP_DOWNLOAD_REFERAL_BY) Download_Count,
    COUNT([customer].ORDER_REFERAL_BY) Order_Count  
FROM
    TBL_CUSTOMER_DETAILS [customer]
RIGHT JOIN
    TBL_PARTNER_DETAILS [partner] ON [customer].SIGN_UP_REFERAL_BY = [partner].USER_GUID 
                                  OR [customer].APP_DOWNLOAD_REFERAL_BY = [partner].USER_GUID 
                                  OR [customer].ORDER_REFERAL_BY = [partner].USER_GUID 
GROUP BY 
    [partner].Partner_Id, [partner].NAME;

But I'm getting a wrong output; can anyone point out what's wrong here or any optimize way to get count download, sign up and order by using partner id by above table schema

sql-server sql
1 Answers

You can use cross apply to unpivot and then reaggregate:

select v.partnerid, p.name, 
       sum(is_signup), sum(is_download), sum(is_order)
from customer c cross apply
     (values (Sign_up_Referral_By, 1, 0, 0),
             (Download_Referral_By, 0, 1, 0),
             (Order_Referral_By, 0, 0, 1)
     ) v(partnerid, is_signup, is_download, is_order) join
     partner p
     on v.partnerid = p.partnerid
group by v.partnerid, p.name;

Here is a db<>fiddle.

user_11440350
Related