User Behaviour Study for a Social eCommerce Platform
Objective: To analyzing the user's database will lead to understanding the business perspective. Behaviour of the users can be traced in terms of business with exploration of the user’s database.
Task 1:Create new schema as ecommerce
Ans:
CREATE DATABASE ecommerce;
USE ecommerce;Task 2: Import .csv file users_data into MySQL
Ans:
/*Task 2: Import .csv file users_data into MySQL
(right click on ecommerce schema -> Table Data import Wizard -> Give path of the file ->
Next -> choose options : Create a new table , select delete if exist -> next -> next)
*/
/* Output:
10:16:22 USE ecommerce 0 row(s) affected 0.000 sec
10:22:41 SHOW SESSION VARIABLES LIKE 'lower_case_table_names' OK 0.000 sec
10:22:41 SHOW DATABASES OK 0.000 sec
10:23:15 SHOW SESSION VARIABLES LIKE 'lower_case_table_names' OK 0.000 sec
10:23:15 SHOW TABLES FROM `ecommerce` like 'users_data' OK 0.000 sec
10:23:30 SHOW SESSION VARIABLES LIKE 'lower_case_table_names' OK 0.000 sec
10:23:30 SHOW TABLES FROM `ecommerce` like 'users_data' OK 0.000 sec
10:24:01 CREATE TABLE `ecommerce`.`users_data` (`identifierHash` bigint, `type` text, `country` text, `language` text, `socialNbFollowers` int, `socialNbFollows` int, `socialProductsLiked` int, `productsListed` int, `productsSold` int, `productsPassRate` double, `productsWished` int, `productsBought` int, `gender` text, `civilityGenderId` int, `civilityTitle` text, `hasAnyApp` text, `hasAndroidApp` text, `hasIosApp` text, `hasProfilePicture` text, `daysSinceLastLogin` int, `seniority` int, `seniorityAsMonths` double, `seniorityAsYears` double, `countryCode` text) OK 0.000 sec
10:24:01 PREPARE stmt FROM 'INSERT INTO `ecommerce`.`users_data` (`identifierHash`,`type`,`country`,`language`,`socialNbFollowers`,`socialNbFollows`,`socialProductsLiked`,`productsListed`,`productsSold`,`productsPassRate`,`productsWished`,`productsBought`,`gender`,`civilityGenderId`,`civilityTitle`,`hasAnyApp`,`hasAndroidApp`,`hasIosApp`,`hasProfilePicture`,`daysSinceLastLogin`,`seniority`,`seniorityAsMonths`,`seniorityAsYears`,`countryCode`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' OK 0.000 sec
10:59:52 DEALLOCATE PREPARE stmt OK 0.000 sec
*/Task 3.Run SQL command to see the structure of table
Ans:
/* Task 3: Run SQL command to see the structure of table*/
DESCRIBE users_data;
/* Output: 11:24:54 DESCRIBE users_data 24 row(s) returned 0.016 sec / 0.000 sec */ 
Task 4: Run SQL command to select first 100 rows of the database
Ans:
/* Task 4: Run SQL command to select first 100 rows of the database */
SELECT *
FROM users_data
LIMIT 100;
/* Output:
11:39:44 SELECT * FROM users_data LIMIT 100 100 row(s) returned 0.000 sec / 0.000 sec
*/Result:
Task 5: How many distinct values exist in table for field country and language?
Ans: For country there are 200 distinct values and for language there are 5 distinct values
/* Task 5: How many distinct values exist in table for field country and language */
SELECT COUNT(DISTINCT(country)), COUNT(DISTINCT(language))
FROM users_data;
/* Output:
11:44:01 SELECT COUNT(DISTINCT(country)), COUNT(DISTINCT(language)) FROM users_data 1 row(s) returned 0.312 sec / 0.000 sec
*/
Task 6. Check whether male users are having maximum followers or female users.
Ans: Female users have maximum followers with 262458 followers as compared to 77038 Male followers.
/* Task 6:Check whether male users are having maximum followers or female users. */
SELECT
gender, SUM(socialNbFollowers)
FROM
users_data
GROUP BY gender;
/* Output:
11:23:33 SELECT gender, SUM(socialNbFollowers) FROM users_data GROUP BY gender 2 row(s) returned 0.359 sec / 0.000 sec
*/Result:
Task 7. Calculate the total users those
- Uses Profile Picture in their Profile
- Uses Application for Ecommerce platform
- Uses Android app
- Uses ios app
Ans:
/* Task 7. Calculate the total users those
Uses Profile Picture in their Profile
Uses Application for Ecommerce platform
Uses Android app
Uses ios app */
# Soln:
SELECT 'Description', 'Total Users'
UNION SELECT
'Has Profile Picture', COUNT(hasProfilePicture)
FROM
users_data
WHERE
hasProfilePicture = 'TRUE'
UNION SELECT
'Has any App', COUNT(hasAnyApp)
FROM
users_data
WHERE
hasAnyApp = 'TRUE'
UNION SELECT
'Has Andriod App', COUNT(hasAndroidApp)
FROM
users_data
WHERE
hasAndroidApp = 'TRUE'
UNION SELECT
'Has ios App', COUNT(hasIosApp)
FROM
users_data
WHERE
hasIosApp = 'TRUE';
/* Output:
19:26:29 SELECT 'Description', 'Total Users' UNION SELECT 'Has Profile Picture', COUNT(hasProfilePicture) FROM users_data WHERE hasProfilePicture = 'TRUE' UNION SELECT 'Has any App', COUNT(hasAnyApp) FROM users_data WHERE hasAnyApp = 'TRUE' UNION SELECT 'Has Andriod App', COUNT(hasAndroidApp) FROM users_data WHERE hasAndroidApp = 'TRUE' UNION SELECT 'Has ios App', COUNT(hasIosApp) FROM users_data WHERE hasIosApp = 'TRUE' 5 row(s) returned 0.484 sec / 0.000 sec
*/Results:
Task 8: Calculate the total number of buyers for each country and sort the result in descending order of
total number of buyers. (Hint: consider only those users having at least 1 product bought.)
Ans:
/* Task 8: Calculate the total number of buyers for each country and sort the result in descending order of
total number of buyers. (Hint: consider only those users having at least 1 product bought.) */
# Soln:
USE ecommerce;
SELECT * FROM users_data;
SELECT
country,
COUNT(productsBought) AS Buyers
FROM
users_data
WHERE
productsBought > 0
GROUP BY country
ORDER BY Buyers DESC;
/* Output:
10:28:32 SELECT country, COUNT(productsBought) AS Buyers FROM users_data WHERE productsBought > 0 GROUP BY country ORDER BY Buyers desc LIMIT 0, 100 62 row(s) returned 0.234 sec / 0.000 sec
*/Results:
Task 9: Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers. (Hint: consider only those users having at least 1 product sold.)
Ans:
/* Task 9: Calculate the total number of sellers for each country and sort the result in ascending order
of total number of sellers. (Hint: consider only those users having at least 1 product sold.) */
# Soln:
SELECT
country,
COUNT(productsSold) AS Sellers
FROM
users_data
WHERE
productsSold > 0
GROUP BY country
ORDER BY Sellers DESC;
/* Output:
10:43:54 SELECT country, COUNT(productsSold) AS Sellers FROM users_data WHERE productsSold > 0 GROUP BY country ORDER BY Sellers DESC 42 row(s) returned 0.140 sec / 0.000 sec
*/Result:
Task 10: Display name of top 10 countries having maximum products pass rate.
Ans:
/* Task 10: Display name of top 10 countries having maximum products pass rate. */
# Soln:
SELECT
country, COUNT(productsPassRate) AS PassR
FROM
users_data
WHERE
productsPassRate > 0
GROUP BY country
ORDER BY PassR DESC
LIMIT 10;
/* Output:
10:49:39 SELECT country, COUNT(productsPassRate) AS PassR FROM users_data WHERE productsPassRate > 0 GROUP BY country ORDER BY PassR DESC LIMIT 10 10 row(s) returned 0.110 sec / 0.000 sec
*/Result:
Task 11: Calculate the number of users on an ecommerce platform for different language choices.
Ans:
/* Task 11: Calculate the number of users on an ecommerce platform for different language choices. */
# Soln:
SELECT
COUNT(language) DiffLanguageChoices
FROM
users_data
WHERE
language <> 'en';
/* Output:
10:58:06 SELECT COUNT(language) DiffLanguageChoices FROM users_data WHERE language <> 'en' 1 row(s) returned 0.125 sec / 0.000 sec
*/Result:
Task 12: Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform. (Hint: use UNION to answer this question.)
Ans:
/* Task 12: Check the choice of female users about putting the product in a wishlist or to like socially
on an ecommerce platform. (Hint: use UNION to answer this question.) */
#Soln:
SELECT * FROM users_data;
SELECT 'Description', 'No. of units'
UNION SELECT
'Products Social Liked', SUM(socialProductsLiked)
FROM users_data
WHERE gender = 'F'
union select 'Products wishlisted', SUM(productsWished) wishlisted
FROM users_data
WHERE gender = 'F';
/* Output:
11:16:05 SELECT 'Description', 'No. of units' UNION SELECT 'Products Social Liked', SUM(socialProductsLiked) units FROM users_data WHERE gender = 'F' union select 'Products wishlisted', SUM(productsWished) wishlisted FROM users_data WHERE gender = 'F' 3 row(s) returned 0.266 sec / 0.000 sec
*/Result:
Task 13: Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
Ans:
/* Task 13: Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question. */
# Soln:
SELECT 'Description', 'No. of people'
UNION SELECT
'Sellers(Male)', COUNT(productsListed)
FROM
users_data
WHERE
gender = 'M' AND productsListed > 0
UNION SELECT
'Buyers(Male)', COUNT(productsBought)
FROM
users_data
WHERE
gender = 'M' AND productsBought > 0;
/* Output:
11:25:53 SELECT 'Description', 'No. of people' UNION SELECT 'Sellers(Male)', COUNT(productsListed) FROM users_data WHERE gender = 'M' AND productsListed > 0 UNION SELECT 'Buyers(Male)', COUNT(productsBought) FROM users_data WHERE gender = 'M' AND productsBought > 0 3 row(s) returned 0.313 sec / 0.000 sec
*/Result:
Task 14: Which country is having maximum number of buyers?
Ans:
/* Task 14: Which country is having maximum number of buyers?*/
# Soln:
SELECT
country, COUNT(productsBought) AS Buyers
FROM
users_data
WHERE
productsBought > 0
GROUP BY
country
ORDER BY
Buyers DESC LIMIT 1;
/* Output:
11:31:49 SELECT country, COUNT(productsBought) AS Buyers FROM users_data WHERE productsBought > 0 GROUP BY country ORDER BY Buyers DESC LIMIT 1 1 row(s) returned 0.141 sec / 0.000 sec
*/Result:
Task 15: List the name of 10 countries having zero number of sellers.
Ans:
/* Task 15: List the name of 10 countries having zero number of sellers. */
# Soln:
SELECT
country, SUM(productsListed) AS Zerolist
FROM
users_data
GROUP BY country
ORDER BY Zerolist
LIMIT 10;
/* Output:
11:44:56 SELECT country, SUM(productsListed) AS Zerolist FROM users_data GROUP BY country ORDER BY Zerolist LIMIT 10 10 row(s) returned 0.312 sec / 0.000 sec
*/Result:
Task 16: Display record of top 110 users who have used ecommerce platform recently.
Ans:
/* Task 16: Display record of top 110 users who have used ecommerce platform recently. */
# Soln:
SELECT
identifierHash, daysSinceLastLogin
FROM
users_data
ORDER BY daysSinceLastLogin
LIMIT 110;
/* Output:
11:56:34 SELECT identifierHash, daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin LIMIT 110 110 row(s) returned 0.156 sec / 0.000 sec
*/Result:
Task 17: Calculate the number of female users those who have not logged in since last 100 days.
Ans:
/* Task 17: Calculate the number of female users those who have not logged in since last 100 days. */
# Soln:
SELECT
COUNT(identifierHash) AS Femaleusers
FROM
users_data
WHERE
gender = 'F'
AND daysSinceLastLogin > 100;
/* Output:
12:03:15 SELECT COUNT(identifierHash) AS Femaleusers FROM users_data WHERE gender = 'F'AND daysSinceLastLogin > 100 1 row(s) returned 0.187 sec / 0.000 sec
*/Result:
Task 18: Display the number of female users of each country at ecommerce platform.
Ans:
/* Task 18: Display the number of female users of each country at ecommerce platform. */
# Soln:
SELECT
country, COUNT(identifierHash) AS Femaleusers
FROM
users_data
WHERE
gender = 'F'
GROUP BY country;
/* Output:
12:09:09 SELECT country, COUNT(identifierHash) AS Femaleusers FROM users_data WHERE gender = 'F' GROUP BY country 192 row(s) returned 0.234 sec / 0.000 sec
*/Result:
Task 19: Display the number of male users of each country at ecommerce platform.
Ans:
/* Task 19: Display the number of male users of each country at ecommerce platform. */
# Soln:
SELECT
country, COUNT(identifierHash) AS Maleusers
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;
/* Output:
12:10:52 SELECT country, COUNT(identifierHash) AS Maleusers FROM users_data WHERE gender = 'M' GROUP BY country 158 row(s) returned 0.234 sec / 0.000 sec
*/Result:
Task 20: Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
Ans:
/* Task 20: Calculate the average number of products sold and bought on ecommerce platform by male users
for each country. */
# Soln:
SELECT
country, AVG(productsSold), AVG(productsBought)
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;
/* Output:
19:15:13 SELECT country, AVG(productsSold), AVG(productsBought) FROM users_data WHERE gender = 'M' GROUP BY country 158 row(s) returned 0.187 sec / 0.000 sec
*/Result:
