CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Sinamate
Sinamate

Reputation: 37

How to count how many customers is returning and how many is new

So I have a customer log-in screen with this information

StartDate ClientID cycleID
01-10-2022 101 100
01-10-2022 102 100
01-10-2022 103 100
01-10-2022 104 100
01-10-2022 105 100
01-11-2022 101 200
01-11-2022 102 200
01-11-2022 104 200
01-11-2022 106 200
01-11-2022 107 200

I want to make a reusable system where I can add a base CycleID(100) and a new CycleID(200) and get the count/list of clientID's that is returning and which are new.

I've looked into creating a CTE but was wondering if a UDF can be created in SQL

Upvotes: 0

Views: 31

Answers (1)

nbk
nbk

Reputation: 49375

You can use conditonal aggregation on a join

SELECT SUM(CASE WHEN s2."ClientID"  IS NOT NULL  THEN 1 ELSE 0 END) repeat
  ,
  SUM(CASE WHEN s2."ClientID"  IS NULL  THEN 1 ELSE 0 END) new_ 
  FROM (SELECT * FROM tab1 WHERE "cycleID" = 200) s1 
  LEFT JOIN (SELECT * FROM tab1 WHERE "cycleID" = 100) s2 
ON s1."ClientID" = s2."ClientID"
repeat new_
3 2

you can add Distinct, if you don't want to count the duplicates

SELECT SUM(CASE WHEN s2."ClientID"  IS NOT NULL  THEN 1 ELSE 0 END) repeat
  ,
  SUM(CASE WHEN s2."ClientID"  IS NULL  THEN 1 ELSE 0 END) new_ 
  FROM (SELECT DISTINCT "ClientID" FROM tab1 WHERE "cycleID" = 200) s1 
  LEFT JOIN (SELECT DISTINCT "ClientID" FROM tab1 WHERE "cycleID" = 100) s2 
ON s1."ClientID" = s2."ClientID"

Upvotes: 1

Related Questions