Find Orders Between Age Range Using SQL | SQL Assignment Help | Codersarts



Objective:

A company sells SubscriptionX to its customers. To guide their efforts in a marketing campaign, one of the company’s stakeholders would like to understand which age group represents their most sticky customers. They have come to you asking for counts of sticky customers by age group over the past year.

Definitions / Database Design:

A sticky customer is defined as a customer who had at least two subscription-related transactions to SubscriptionX in the past year.









You write a SQL query that outputs this data:











Challenge:

How would you query these tables to produce the resulting data set? (You may use any SQL variant you are most familiar with (PostgreSQL, MySQL, etc.)


Solution SQL QUERY:


select 
 case
 when user.age <20 then '<20'
 when user.age between 20 and 29 then '20-29'
 when user.age between 30 and 39 then '30-39'
 when user.age between 40 and 49 then '40-49'
 when user.age between 50 and 59 then '50-59'
 when user.age between 60 and 69 then '60-69'
 when user.age <=70 then '<=70'
 END as age_group, 
 Count(*) as user_quantity
 from user inner join orders on
 user.user_id = orders.user_id
 group by age_group 
ORDER BY
  CASE age_group
 WHEN '<20' THEN 1
 WHEN '20-29' THEN 2
 WHEN '30-39' THEN 3
 WHEN '40-49' THEN 4
  WHEN '50-59' THEN 5
 WHEN '60-69' THEN 6
 WHEN '<=70' THEN 7
 ELSE 8
 END


Contact us for this machine learning assignment Solutions by Codersarts Specialist who can help you mentor and guide for such machine learning assignments.


If you have project or assignment files, You can send at contact@codersarts.com directly

Contact Us

Tel: (+91) 0120  4118730  

Time :   10 : 00  AM -  08 : 00 PM IST 

Registered address: G-69, Sector 63, 

 Noida - 201301, India

We Provide Services Across The different countries

USA    Australia   Canada   UK    UAE    Singapore   New Zealand    Malasia   India   Ireland   Germany

CodersArts is a Product by Sofstack Technology Solutions Pvt. Ltd.

  • CodersArts | Linkedin
  • Instagram