A company named The Logis Group (LG), is a business-to-business consulting services company that provides consulting services to support the work of its customer companies. LG's customer companies are from many business and industry sectors including manufacturing, distribution, retail, transportation, and others. Besides a fixed amount of annual subscription fee, customer companies also pay variable charges, which are based on the extent to which their employees use LG's products and services.

LG's business analytics (BA) department is responsible for tracking users' online activity and engagement in order to provide better products and services to its customers. (NOTE: the term users refers to employees of the customer companies.) Various BA teams conduct studies, carry out projects to address specific business problems, and perform ad-hoc analyses to support business decisions. You are one of the business analysts in the BA department.

LG defines user activity as an engagement with its online portal, which includes the customer company employees (users) having made some type of server call by interacting with the company’s website/web server. Such events are listed as “engagement” in the event_type column of the EVENTS table.

Data:

The following file (events.csv) is available on Canvas (Files > Data Files). Columns in this file are listed below.

EVENTS
user_id:	The ID of the user logging the event. Can be joined to user_id in either of the other tables.
occurred_at:	The time the event occurred.
event_type:	The general event type. There are two values in this dataset: signup_flow: refers to anything occurring during the process of a user's authentication, engagement: refers to general product usage after the user has signed up for the first time.
event_name:	The specific action the user took - possible values include the following: create_user: User is added to the company’s database during signup process enter_email: User begins the signup process by entering her email address enter_info: User enters her name and personal information during signup process complete_signup: User completes the entire signup/authentication process home_page: User loads the home page like_message: User likes another user's message login: User logs into her/his account search_autocomplete: User selects a search result from the autocomplete list search_run: User runs a search query and is taken to the search results page search_click_result_X: User clicks search result X on the results page, where X is a number from 1 through 10. send_message: User posts a message view_inbox: User views messages in her inbox
location:	The country from which the event was logged (collected through IP address).
device:	The type of device used to log the event.

Question to Answer:

Please conduct necessary analysis using SQL to investigate the following question:

In which of the following countries, has user activity or engagement dropped in recent weeks? United States, Japan, Brazil, France. events.csv:

 

user_id 10522 10522 10522 10522 10522 10522 10612 10612 10612 10612 10612 10612 10612 10612 10612 10612 10612 10612 10736 10736 10736 10965 10965 11020 11020 11020 11020 11020 11020 11020 11020 11037 11037 11037 event_type event_name location 5/2/14 11:02 engagement login Japan 5/2/14 11:02 engagement home_page Japan 5/2/14 11:03 engagement like_messag Japan 5/2/14 11:04 engagement view_inbox Japan 5/2/14 11:03 engagement search_run Japan 5/2/14 11:03 engagement search_run Japan 5/1/14 9:59 engagement login Netherlands iphone 5 5/1/14 10:00 engagement like_messag Netherlands iphone 5 5/1/14 10:00 engagement send_messa Netherlands iphone 5 5/1/14 10:01 engagement home_page Netherlands iphone 5 5/1/14 10:01 engagement like_messag Netherlands iphone 5 5/1/14 10:02 engagement home_page Netherlands iphone 5 5/1/14 10:02 engagement view_inbox Netherlands iphone 5 5/1/14 10:03 engagement like_messag Netherlands iphone 5 5/1/14 engagement home_page Netherlands iphone 5 5/1/14 10:04 engagement send_messa Netherlands iphone 5 5/1/14 10:04 engagement like_messag Netherlands iphone 5 5/1/14 10:05 engagement send_messa Netherlands iphone 5 5/9/14 17:52 engagement login Austria iphone 4s iphone 4s 5/9/14 17:53 engagement like_messag Austria 5/9/14 17:53 engagement send_messa Austria 5/15/14 13:52 engagement login iphone 4s Finland windows sur windows sur macbook air macbook air macbook air macbook air macbook air macbook air macbook air macbook air occurred_at 5/15/14 13:53 engagement home_page Finland 5/8/14 9:15 engagement login Japan 5/8/14 9:15 engagement home_page Japan 5/8/14 9:16 engagement view_inbox Japan 5/8/14 9:16 engagement home_page Japan 5/8/14 9:17 engagement view_inbox Japan 6/8/14 9:17 engagement send_messa Japan 5/8/14 9:18 engagement view_inbox Japan 5/8/14 9:19 engagement like_messag Japan 5/1/14 7:25 engagement login device dell inspiron dell inspiron dell inspiron dell inspiron dell inspiron dell inspiron United Kingd iphone 4s 5/1/14 7:25 engagement like_messag United Kingd iphone 4s 5/1/14 7:26 engagement home page United Kingd iphone 4s user_type 3 3 3 3 3 LLL 3 1 1 1 1 1 1 1 1 1 1 1 1 2 2 233 2 2 222 2 2 2 1 1 1