# 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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.devtodev.com/~/changes/KHSfVuDz62f75T3NzrM9/reports-and-functionality/space-related-reports-and-functionality/sql/sql-tips-1.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.