# SQL tips

{% hint style="success" %}
Check out this guide to avoid mistakes and create [optimized SQL queries](https://www.devtodev.com/resources/articles/sql-mistakes-to-avoid-a-guide-to-optimizing-your-queries)!
{% endhint %}

## **Conditions with dates**

1. To set the difference between two dates, it is better to use the following expression: **eventtime - created <= interval ‘10 min’** (instead of eventtime - created <= 600000)
2. To set a relative date, instead of created <= to\_timestamp('2020-11-01 12:59:59', 'YYYY-MM-DD HH24:MI:SS') it is better to use **created < date ‘2020-11-02’ or created <= timestamp ‘2020-11-01 12:59:59’** if you need to add the exact time.
3. To include a specific date to the query result (for example 30th of November 2020) you can apply the following condition: **created < date '2020-12-01' or created <= timestamp '2020-11-30 23:59:59'**\
   The first option is preferable.

## **Countries**

The country names are stored in two-letter code form, such as ‘CN’, ‘FR’, ‘GB’, etc.Therefore, instead of the full country name (**‘China’**) you can use a code (**'CN'**) or use the countries dictionary - **and p.country <> ANY(select code from countries where name = 'China'**)

## DAU (Daily active users)

Active user is a user who generates any event (basic, custom, Start session event) via SDK or API over a certain period of time, for example, a day for DAU. You can use a more convenient table with users’ activity to calculate DAU: users get added only once per day if they are active.

```sql
select eventdate, count(devtodevid)
from p103198.dau
where eventdate >= current_date - interval '1 month'
group by 1
order by 1 asc
```

It corresponds to the DAU metric in the Basic Metrics report. Users marked as cheater or tester are not included in this table.

## **User segments:**&#x20;

To see the segments the user belongs to:

```sql
SELECT  devtodevid, segmentvalues
FROM p102968.users
WHERE segmentvalues is not null 
```

To filter users and select those who belong to the 'test\_segment' segment:

<pre class="language-sql"><code class="lang-sql"><strong>SELECT  devtodevid
</strong>FROM p102968.users
WHERE segmentvalues->'test_segment' is true 
or segmentvalues->'other_segment' is not true
</code></pre>

To filter users and select those who belong to the specific A/B test group:

```sql
select devtodevid, abtestvalues
from p105424.users u
where u.lasttime >= current_date - interval '1 year' 
and u.abtestvalues->'test_name.A' is not null
```

## Currency conversion&#x20;

When we receive payment information, we convert it into United States Dollar (USD) using the latest exchange rate. Thus, devtodev stores information about payment amounts in USD.

However, if you want to calculate your financial metrics in Euro (EUR), Japanese Yen (JPY), or any other currency, you can use the *currencies\_historical* table which contains exchange rates of multiple currencies to the U.S. dollar for each day.

```sql
select 
  p.date,
  p.usd,
  round(p.usd/eur.rate::numeric, 2) as eur
from (select  
        eventtime::date as date,
        sum(p.priceusd) as usd
      from p102968.payments p
      where p.eventtime >= current_date - interval '30 day' and p.eventtime < current_date
      group by 1) p
  left join public.currencies_historical eur on 
      eur.source = 'EUR' 
      and eur.update_date = p.date
order by p.date
```


---

# 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/reports-and-functionality/space-related-reports-and-functionality/sql/sql-tips.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.