SQL Assignment Question

DWH diagram of an e-commerce platform

Please, see the following DWH diagram of an e-commerce platform.

During a user’s lifecycle, before as well as after registering on the platform, any visits of platform webpages are tracked (if possible).

In the informations table the total amount of page visits is stored (info_page_visits) for the respective timestamp of the visit and the respective used device (phone, tablet, desktop, etc.).

At the moment the user registers on the platform, a row is created in the users table. Additionally, in case the user visited the platform before, rows are created in the activities table for all of the user’s previous visits with the respective device_ID and timestamp of the visit (activity_timestamp = info_timestamp). After the registration any user activity on the platform is also stored in the activities table. The activity_is_last field is updated accordingly and always true for only one row per user in this table.

When the registered user buys something on the platform, this creates a row in the sales table and when the item is paid, a row in the payments table.

The relations between tables are defined by the names, e. g. sales.sale_user_ID refers to users.user_ID. A user does not need to have any activities or sales.

For the SQL queries, please choose any SQL dialect you prefer.

  • 1) Please write the SQL SELECT query for the result described in a) and answer the questions in b). a) The number of daily active users per device
  1. b) What kind of issue could occur, when using this result for further analysis, e.g. for calculating the total number of daily active users? How would you adjust the query to fix it?

Please ignore users here, who are not registered.

2) Please write the SQL SELECT queries for the following results:

  1. a) The total pay_amount per city for sales in 2019
  2. b) The number of users, for whom the last activity was done with device_name = “phone”,

but who have also at least one additional activity with another device

  1. c) Per day in 2019 the number of page visits and registrations

3) Please write the SQL SELECT queries for the following results:

  1. a) Per registration date and last device the number of users and the number of sales done by users within 3 days after the registration.
  2. b) Per visit day in 2019 the number of page visits and registrations

Explain in writing: What would be measured with 2 c) and what with this result?

SQL Assignment

+ Submit Your Assignment Here

AssignmentHippo Features

On Time Delivery

Our motto is deliver assignment on Time. Our Expert writers deliver quality assignments to the students.

Plagiarism Free Work

Get reliable and unique assignments by using our 100% plagiarism-free.

24 X 7 Live Help

Get connected 24*7 with our Live Chat support executives to receive instant solutions for your assignment.

Services For All Subjects

Get Help with all the subjects like: Programming, Accounting, Finance, Engineering, Law and Marketing.

Best Price Guarantee

Get premium service at a pocket-friendly rate at AssignmentHippo

FREE SAMPLE FILE

Client Review

I was struggling so hard to complete my marketing assignment on brand development when I decided to finally reach to the experts of this portal. They certainly deliver perfect consistency and the desired format. The content prepared by the experts of this platform was simply amazing. I definitely owe my grades to them.

Tap to Chat
Get instant assignment help