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:
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 email@example.com directly