MySQL Query

rank

# see leetcode 2173 https://leetcode.com/problems/longest-winning-streak/description/
select
    player_id,
    result,
    RANK () OVER (
        PARTITION BY player_id,
        result
        ORDER BY
            match_day
    ) as my_rank
from
    Matches
order by
    match_day

generate sequence

# 1 to 100
WITH RECURSIVE seq AS (
    SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 100
    )

create help tables

with platforms as (
select 'Android' as platform
union
select 'IOS' as platform
union
select 'Web' as platform
),

 activities as (
  select 'Reading' as experiment_name
  union
  select 'Sports' as experiment_name
  union
  select 'Programming' as experiment_name
)

More Queries

#day function converts timestamp to day. 2023-10-11 23:33:33 to 11

select day(created), partner_fid, count(*) from cpa_conversion where created>="2024-9-1" group by day(created), partner_fid;