SQL tips

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')

Using variables

The following syntax defines how to declare a variable:

DECLARE { @LOCAL_VARIABLE data_type [ = value ] }

Examples:

declare @test_var float8 = 1.1111, @ts timestamp = '2021-01-01'; In order to use the variable in the query use the following syntax: select round(@test_var, 2), @ts

This is example of a query where variables were used:

declare @maxLevel int = 10, -- variable declaration
@date_from date = '2020-11-01',
@date_to date = '2021-02-01';
select to_char(eventtime, 'yyyy-mm') as month
, round(sum(p.priceusd)::numeric, 2) as revenue
, count(eventtime) as payments
from pXXX.payments p
where eventtime >= @date_from and eventtime < @date_to -- using variables in the query, space before the "@" sign is important!!
and level < @maxLevel
group by 1
order by 1 asc

User segments:

To see the segments the user belongs to:

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

To filter users and select those who belong to the 'test_segment' segment:

SELECT devtodevid
FROM p102968.users
WHERE segmentvalues->'test_segment' is true
or segmentvalues->'other_segment' is not true