# SQL Query examples

## Example 1

The result of this query is a list of user events for the last 2 days sorted by the amount of times the event was performed.

```sql
with last_ev as 
    (select distinct devtodevid, max(eventtime) as eventtime
    from p104704.custom_events 
    where created >= current_date - interval '2 days'
    group by 1)
select name, count(distinct devtodevid)
from 
  (select distinct q1.devtodevid, name
  from p104704.custom_events q1 
    join last_ev q2 using (devtodevid, eventtime)) q3
group by 1
order by 2 desc
```

## Example 2

The result of this query is a list of last user events before they churn. The churn period is specified.

```sql
with last_ev as
    (select distinct devtodevid, max(eventtime) as eventtime
    from p104704.custom_events ev 
    join p104704.users us using(devtodevid)
    where us.lasttime <= current_date - interval '7 days'  -- churn period
    and ev.created >= current_date - interval '30 days'
    group by 1)
select name, count(distinct devtodevid)
from
  (select distinct q1.devtodevid, name
  from p104704.custom_events q1
    join last_ev q2 using (devtodevid, eventtime)) q3
group by 1
order by 2 desc
```

## Example 3

The result of this query is a timestamp of the last event with numeric value.

```sql
select tm(eventtime) as "event timestamp", data
from p104704.user_property_updates 
where data::text SIMILAR TO '%customUserProperyName":\d%' 
order by eventtime desc 
limit 1
```

The result of this query is a timestamp of the last event with text (string) value.

```sql
select tm(eventtime) as "event timestamp", data
from p104704.user_property_updates
where data::text SIMILAR TO '%customUserProperyName":"%' 
order by eventtime desc 
limit 1
```

## Example 4

Use this query to find the users with decreasing player level (**events with decreasing player level** alert). The result of this query is a list of users and their previous and current levels.

```sql
select devtodevid, prev_event_lvl, cur_event_lvl
from(
  select devtodevid
  , lag(eventlevel,1) over (partition by devtodevid order by eventtime) as prev_event_lvl
  , eventlevel as cur_event_lvl
  from p104704.levelups
  where created >= current_date - interval '7 day' and eventtime < current_date
) t
where prev_event_lvl > cur_event_lvl
order by devtodevid
```

## Example 5

Use this query to calculate user retention. The result of this query is a table that contains the number of new users per day and the number of returning users on the 0th, 1st, 2nd, 3rd, 7th, and 14th day after installation.

```sql
with return as (
  select created::date as date_, 
    count(devtodevid) filter (where retentionday = 0) as R0,
    count(devtodevid) filter (where retentionday = 1) as R1,
    count(devtodevid) filter (where retentionday = 2) as R2,
    count(devtodevid) filter (where retentionday = 3) as R3,
    count(devtodevid) filter (where retentionday = 7) as R7,
    count(devtodevid) filter (where retentionday = 14) as R14
  from p102968.returns_by_24h 
  where created::date >= current_date - interval '1 month'
  and eventtime >= current_date - interval '1 month'
  group by 1
  )
, us as (
  select created::date as date_, count(1) AS regs
  from p102968.users
  where created::date >= current_date - interval '1 month'
  group by 1
  )
select us.date_, regs, r0, r1, r2, r3, r7, r14
from us 
  left join return r on us.date_=r.date_
order by 1
```

## Example 6&#x20;

See revenue for a year by monthly cohorts. The result table will show revenue by each cohort for each month.&#x20;

You can modify the periods for the cohorts and payment events. &#x20;

```sql
select to_char(created, 'yyyy-mm') as month 
, count(distinct devtodevid) as users_in_cohort 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-01-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-02-01', 'yyyy-mm-dd')) as jan_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-02-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-03-01', 'yyyy-mm-dd')) as feb_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-03-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-04-01', 'yyyy-mm-dd')) as mar_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-04-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-05-01', 'yyyy-mm-dd')) as apr_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-05-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-06-01', 'yyyy-mm-dd')) as may_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-06-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-07-01', 'yyyy-mm-dd')) as june_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-07-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-08-01', 'yyyy-mm-dd')) as july_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-08-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-09-01', 'yyyy-mm-dd')) as aug_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-09-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-10-01', 'yyyy-mm-dd')) as sep_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-10-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-11-01', 'yyyy-mm-dd')) as oct_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-11-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-12-01', 'yyyy-mm-dd')) as nov_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-12-01', 'yyyy-mm-dd') and p.eventtime < to_date('2025-01-01', 'yyyy-mm-dd')) as dec_rev
from p104704.payments p
where created >= to_date('2024-01-01', 'yyyy-mm-dd') and created < to_date('2025-01-01', 'yyyy-mm-dd') -- new users during this period
and tester is false 
and cheater is false
group by 1
order by 1 asc
```