Queries

blankhans.sql

with
  world_ccu as (select _ from (select cm._, rank() over (partition by cm.world_id order by cm.created_at desc) as r from ccu_metrics cm) tccu where tccu.r = 1),
  world_mau as (select c.world_id, count(c.id) from "characters" c group by c.world_id),
  world_queue_tickets as (select q.world_id, count(q.id) as queue_length, max(AGE(q.last_check, q.created_at)) as queue_wait from queue_tickets q group by q.world_id)
select to_char(now() at time zone 'utc', 'YYYY/MM/DD HH24:MI:SS') as snapshotTime, w.id, w.vip, w.region, w.world_uuid, to_char(w.created_at at time zone 'utc', 'YYYY/MM/DD HH24:MI:SS') as worldCreatedDate, t2.count as mau, t1.count as ccu, coalesce(t3.queue_length,0) as queue_length, coalesce(t3.queue_wait, now() - now()) as queue_wait, to_char(t1.created_at at time zone 'utc', 'YYYY/MM/DD HH24:MI:SS') as lastCCUUpdate
from worlds w
inner join world_ccu as t1 on (t1.world_id = w.id)
inner join world_mau as t2 on (t2.world_id = w.id)
left join world_queue_tickets as t3 on (t3.world_id = w.id)
--where w.world_uuid='bdc48fb1-f7e8-4602-8423-eb5db11f2c1f'
order by w.region, w.created_at;





update "characters" 
set deleted_at=updated_at
where id in (select t1.id from (
with usr as (select u.* from users u
where u.backend_id in ())
select distinct c.id, c.updated_at, c.deleted_at from "characters" c
where c.user_id in (select id from usr) and c.deleted_at is null
) t1);


with usr as (select u.* from users u
where u.backend_id in ())
select distinct usr.backend_id, c.id, c.character_uuid, c.updated_at, c.deleted_at from "characters" c
join usr on (usr.id = c.user_id)
where c.user_id in (select id from usr);


select u.id, u.backend_id, c.* from users u
join "characters" c on (c.user_id=u.id)
where u.backend_id ='';

select * from "characters" c where c.character_uuid = '07068d12-bd48-499e-af43-49f5e275b177';

with worldcounts as (
	select t1.world_id, count(*) as mau from (select * from "characters" c where c.deleted_at is null) t1 group by t1.world_id having world_id is not null
)
select w.world_uuid, wc.mau, w.region, w.created_at
from worlds w
join worldcounts wc on (wc.world_id = w.id);

select * from worlds w where w.world_uuid = 'b371f3c3-5709-4a4d-a373-1a661138ac16';


WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;



with abUsers(backend_id, character_uuid) as (values 
)
select ab.*, c.id
from abUsers ab
left join "characters" c on (ab.character_uuid = c.character_uuid)
where c.id is null;


select u.*, c.* from "characters" c 
join users u on (u.id=c.user_id)
where c.deleted_at is null and u.backend_id  ='cc7c977002484ddc93a98e6283373088';

select c.* from "characters" c where c.id=33;



select * from worlds w where w.world_uuid ='438d6327-859d-4f92-9406-9fba1b38d81d';

select * from worlds w where w.world_uuid ='e9041fd1-d9b4-4936-922f-74fecfa110d1';

select u.*, c.* from users u
join "characters" c on (c.user_id=u.id)
where u.backend_id='5e9439d3f1f442dbb4561320488b9263';

select * from "characters" c where id = 46;

select count(u.*) from users u;

select * from worlds w;

select now();

select sum(1), character_uuid  from "characters" c group by character_uuid order by character_uuid;

select count(*) as count, max(c.created_at) as last_insert, c.world_id from "characters" c where world_id is not null group by c.world_id ;

select * from world_ccu_mmu_snapshot order by snapshottime desc;

select w.region, w.world_uuid, count(c.id) from "characters" c 
inner join worlds w on (w.id = c.world_id)
group by w.region, w.world_uuid
order by w.region, w.world_uuid ;

select w.region, w.world_uuid, c.count as ccu, c.created_at from (select cm.*, rank() over (partition by cm.world_id order by cm.created_at desc) as r from ccu_metrics cm) c 
inner join worlds w on (w.id = c.world_id)
where c.r = 1
order by w.region, w.world_uuid ;




select * from users u where u.backend_id ='a4612e8c72a345ad8ebc70f97c721da0';

select * from worlds w where w.id =6;

select count(u.*) from users u;

blankhans_sql.md

‎‎​