SQL datetime query

Problem statement -

There is a table, named app_strt , with two columns -

  1. user_id (integer) - This column has ids of users of an android application. Each user has a unique id. Example - 121

  2. timestamp (datetime field) - This is the timestamp when the application is launched by a user. Example - 21/07/2020 16:11:11

Whenever any user launches the application, a new row is added to the table. In the new row, user id will be assigned based on who launched the application and timestamp will be the date and time when the app was launched by that user.

What percent of total users use the application on their day 2? Day 2 here means a day after a user installs the application. For example if you installed the application on 8th September then 8th September is considered day 1 for you. 9th September is considered day 2 for you. So each user will have his own day 1, day 2 and so on, based on what date he installed the application. Need to write query for calculating - What percent of total users use the application on their day 2?

Is there a link to the lesson?
What code have you written so far?

Though, this sounds more like homework or a problem for a test. We don’t give out answers for school tests/homework.

Not a school / class homework . It was asked as a part of interview to me . I didn’t know what logic to apply here . So just looking for that to enhance my knowledge of sql .

Hello @princegargg ,

Thanks for giving us the context of the question.

Here’s where I would start with the thought process:

  1. Try mapping out some data first.
user_id     timestamp
=======     ============
1           Oct 1
1           Oct 2
1           Oct 5
2           Oct 1
2           Oct 2
3           Oct 1
3           Oct 1
4           Oct 2
4           Oct 3
4           Oct 4
  1. Write a select for all the records of the users for day 1 (first record for each user in app_strt). Use this select in a temporary query.

  2. Write another select to find records that are one day, +1, after their first day from the query in Step 2.

-- Step 2.  Users day 1 query
WITH tmp_query AS
(SELECT *
 FROM  app_strt
WHERE 
ORDER BY user_id, timestamp)
-- Step 3.
SELECT *
FROM app_strt
WHERE (app_strt.timestamp + 1) = tmp_query.timestamp

There would be some additional fine tuning required plus calculating the percentage.
I hope that helps.

Here’s more info on using the WITH clause: TutorialScan.com

2 Likes