Datetime() function

1 Day Retention 3 - Exercise

In this exercise, a datetime() function was used by I am not sure what it does.
It seems to take in 2 parameters but can anyone tell me what it returns?

date(g1.created_at) = date(datetime(g2.created_at, ‘-1 day’))
Isolating datetime(g2.created_at, ‘-1 day’) itself,
can anyone tell me, in the context of this exercise, what will it return before it is passed on to the date() function which will return then return a date?

Hi there.
Ah, I recall this from awhile ago when I took this as it was part of a SQL Intensive (a six week course when they [CC] offered intensives).

Let’s see…I believe you’re trying to find out how retention rates for each DISTINCT user and how long they have played each game and joining the table on itself.
" Now we’ll join gameplays on itself so that we can have access to all gameplays for each player, for each of their gameplays.

This is known as a self-join and will let us connect the players on Day N to the players on Day N+1. In order to join a table to itself, it must be aliased so we can access each copy separately."

and, " Now that we have retained users as count(distinct g2.user_id) and total users as count(distinct g1.user_id) , divide retained users by total users to calculate 1 day retention!"

thanks for the reply lisalisaj!
I have learnt what the datetime() function is about in another SQL course and it makes it much clearer how it connects the players on Day N to the players on Day N+1 by equating date(g1.created_at) = date( datetime(g2.created_at, ‘-1 day’) ).
I believe it says g1 date (this is Day N) = g2 date minus 1 day (essentially this is Day N + 1) :slight_smile: