SQL Tutorials

๐—ก๐—ฒ๐˜„ ๐—ฎ๐—ป๐—ฑ ๐—ฅ๐—ฒ๐—ฝ๐—ฒ๐—ฎ๐˜ ๐—–๐˜‚๐˜€๐˜๐—ผ๐—บ๐—ฒ๐—ฟ๐˜€ ๐—น๐—ผ๐—ด๐—ถ๐—ฐ ๐—ถ๐—ป ๐—ฆ๐—ค๐—Ÿ

๐™’๐™๐™ฎ ๐™˜๐™–๐™ก๐™˜๐™ช๐™ก๐™–๐™ฉ๐™š ๐™ง๐™š๐™ฅ๐™š๐™–๐™ฉ ๐™˜๐™ช๐™จ๐™ฉ๐™ค๐™ข๐™š๐™ง๐™จ?
1. Repeat Customers spend more on each purchase
2. Repeat Customers are more likely to shop with you again and again
3. Repeat Customers are our business promoters
4. Businesses spend more on your key times like festive rush

๐™’๐™๐™ฎ ๐™ฃ๐™š๐™ฌ ๐™˜๐™ช๐™จ๐™ฉ๐™ค๐™ข๐™š๐™ง๐™จ ๐™–๐™ง๐™š ๐™ž๐™ข๐™ฅ๐™ค๐™ง๐™ฉ๐™–๐™ฃ๐™ฉ?
1. Customer Acquisition Cost (CAC) is an important metric. The lesser the CAC, the better the business is.
2. Helps businesses to identify if their model is a viable one.
3. Every business must understand their CAC as it helps them in investing wisely and scale their business.
4. They get an idea of how much revenue is needed for better sustenance and growth.

Since we have used CTEs in this example, it will be easy to understand the logic and also the query performance will be better.

๐—ก๐—ผ๐˜๐—ฒ: The timeframe for calculating the new & repeat customers can be changed by introducing a where clause in the CTEs.

REQUIREMENT:

The orders placed by consumers in a shop on a daily basis are listed in the
table. We must keep track of each day’s totals for both the number of

customers who were new to the store and those who had previously visited

(new customers and repeat customers).

Expected Results

Query

 with as First_Visit

(

 select Customer_ID, min (Order_date) as First_Visit_Date

 from Orders

 group by Customer_ID

),

Visit_Flag as

(

 select o.*,F.First_Visit_Date,

 O.Order_Date=F.First_Visit_Date then 1 else 0 end as

First_Visit_Flag,

case when O.Order_Date<>F.First_Visit_Date then  1 else  0 end as 
Repeat_Visit_Flag

 from Orders O

 inner join First_Visit F on  O.Customer_ID= F.Customer_ID

)

 Select Order_Date , sum (First_Visit_Flag) as New_Customers,

sum (Repeat_Visit_Flag) as  Repeated_Customers,

count(Customer_ID) as Total_Customers

 Visit_Flag

 Order_Date
Important Notice for college students

If youโ€™re a college student and have skills in programming languages, Want to earn through blogging? Mail us at geekycomail@gmail.com

For more Programming related blogs Visit Us Geekycodes. Follow us on Instagram.

Please subscribe to our Youtube Channel by clicking on the youtube icon given below

Leave a Reply

%d bloggers like this: