Hunt Details: Claw Shot City

[AardWolf]

Pursuant to #231 this is the DDL for creating and populating the table. And spot checking

DDL changes:

-- Make the hd table
CREATE TABLE mhhunthelper.hd_claw_shot_city (
    hunt_id INT NOT NULL,
    at_boss SMALLINT NOT NULL,
    poster_type varchar(20) NOT NULL,
    is_rare_poster TINYINT NOT NULL
);

-- Populate it from existing data
insert into mhhunthelper.hd_claw_shot_city 
select hunt_id, max(at_boss) at_boss, max(poster_type) poster_type, 
    max(is_rare_poster) is_rare_poster
from (
    select hd.hunt_id, case when dt.name = 'at_boss' then 
        case when dv.name = 'true' then 1 else 0 end
        end at_boss,
        case when dt.name = 'poster_type' then REPLACE(dv.name, 'Rare ', '') end poster_type,
        case when dt.name = 'poster_type' then 
            case when instr(dv.name, 'Rare ') then 1 else 0 end 
            end is_rare_poster
    from hunt_details hd 
        inner join detail_types dt
            on hd.detail_type_id = dt.id 
            and dt.name in ('at_boss', 'poster_type')
        inner join detail_values dv 
            on hd.detail_value_id = dv.id 
) as details 
group by hunt_id
;

Some checks:

-- Find all poster types
select dv.name, count(*)
from hunt_details hd 
    inner join detail_types dt 
        on hd.detail_type_id = dt.id 
        and dt.name = 'poster_type'
    inner join detail_values dv 
        on hd.detail_value_id = dv.id 
group by dv.name
ORDER BY dv.name ;

-- Numbers should match
select poster_type, sum(is_rare_poster), count(poster_type)
from hd_claw_shot_city hcsc 
group by poster_type
order by poster_type ;

select hd.hunt_id, dt.name, dv.name
from hunt_details hd 
    inner join detail_types dt
        on hd.detail_type_id = dt.id 
        -- and dt.name in ('at_boss', 'poster_type')
    inner join detail_values dv 
        on hd.detail_value_id = dv.id 
where hunt_id = 35445527
;
select * from hd_claw_shot_city where hunt_id = 35445527
;

Cleaning Up (get counts, make sure they match, then delete)

-- delete 
select count(*) -- 149,344
from hunt_details as hd
where hd.hunt_id in (select hunt_id from hd_claw_shot_city)
  and hd.detail_type_id in (47, 48);

select count(*) * 2 from hd_claw_shot_city hcsc ;
 ;

[AardWolf]

Deployed!