Request #2:  Leveling Off Of Member Growth ????

The next request in your inbox is from a member of the LetsMeet Growth team. They are part of the Sales team that is responsible for growing the membership of LetsMeet year-over-year.
 

Hey there, 

I'm working on a report on membership growth to date for Cat and after scanning the latest numbers noticed that it appears membership growth might be leveling off. 

I'm wondering if you can dig into the year-over-year membership growth for the whole organization. I'm worried there might be some errors in our data collection. 

Engineering has already informed me of at least one error regarding the city information in our grp_member table. It looks like you might need to amalgamate some smaller suburbs into into their wider geographic area (i.e. East Chicago and West Chicago should just be Chicago). I'll send you an attachment with our required breakdown.

Please include any insights into why membership doesn't seem to be growing like it used to.

Let me know if you can get to this by end of day.

Cheers, 

Sammy
 

In their follow up e-mail Sammy included a helpful reminder that the joined column in the grp_member table represents the date a member joined a specific group, not the date they joined LetsMeet. They suggest instead that you use the date a member joined their first group as a proxy for when they joined the site.

2. Find a query that shows the growth of LetsMeet over the years based on the number of members who joined each year.
(Hint: Consider using a subquery to find the YEAR(MIN(joined)) and grouping them by member_id.)

 

3. In the grp_member table, use the appropriate DML to rename the smaller cities to the name of their larger urban areas. Based on the attachment from Sammy, they should be combined as follows:

• Chicago - East Chicago, West Chicago, North Chicago, Chicago Heights, Chicago Ridge, Chicago
• San Francisco - San Francisco, South San Francisco
• New York - New York, West New York
  (Hint: Don't forget to SET SQL_SAFE_UPDATES = 0; 
  Use UPDATE, SET, WHERE. These changes should be permanent.)

 

4. Time to see if there are any differences in year over year growth by city! Write three new queries that show the annual growth of LetsMeet for the larger geographical areas of Chicago, San Francisco, and New York. 
(Hint: You can reuse your query from Step 2 here by adding a WHERE clause.)

5. You decide you want to dig deeper into membership growth specifically in 2017, the year when growth in membership seems to slow the most. Write a query to examine the month by month growth for LetsMeet in 2017. What do you notice about the resulting table?

(Hint: This query will be similar to your previous queries. Instead of looking at YEAR a member joined, you will need to look at MONTH. You will also need to use a WHERE clause to filter your results to the YEAR 2017.

Consider how you need to group these results if you are examining month by month instead of year by year.)


This is the ERD with the tables and columns:
 

 

grp_member member_id INT(11) dity VARCHAR(45) country CHAR(2) hometown VARCHAR(45) joined DATETIME 1 member_name VARCHAR(45) member_status ENUM(...) visited DATETIME group_id INT(11) Indexes category ?category_id INT(11) category_name VARCHAR(45) shortname VARCHAR(45) Indexes venue venue_id INT(11) address 1 VARCHAR(200) address 2 VARCHAR(200) dity VARCHAR(45) country CHAR(2) distance DECIMAL(10,2) sort_name VARCHAR(45) lat DECIMAL(10,8) Hh # localized_country_name VARCHAR(45) lon DECIMAL(12,8) venue_name VARCHAR(300) phone BIGINT(20) grp group_id INT(11) category_id INT(11) city_id INT(11) +created DATETIME description LONGTEXT join_mode ENUM(...) H+ group_name VARCHAR(150) rating DECIMAL(4,2) city city_id INT(11) city VARCHAR(45) state CHAR(2) zip INT(11) country CHAR(2) localized_country_name VARCHAR(45) + distance DECIMAL(12,3) longitude DECIMAL(12,8) latitude DECIMAL(12,8) member_count INT(11) ranking INT(11) Indexes who VARCHAR(85) Indexes visibility ENUM(...) members INT(11) state CHAR(2) zip INT(11) RAND() DOUBLE Indexes event ?event_id VARCHAR(75) created DATETIME description LONGTEXT duration INT(11) event_url VARCHAR(250) group_id INT(11) headcount INT(11) how_to_find_us VARCHAR(250) maybe rsvp_count INT(11) event_name VARCHAR(95) photo_url VARCHAR(250) rsvp_limit INT(11) event status ENUM(...) event time DATETIME updated DATETIME utc_offset INT(11) venue_id INT(11) visibility ENUM(...) waitlist_count INT(11) why VARCHAR(45) yes_rsvp_count INT(11) Indexes