Skip to content

Instantly share code, notes, and snippets.

View kovid-r's full-sized avatar
Working from home

Kovid Rathee kovid-r

Working from home
View GitHub Profile
kovid-r / devstronomy_data.sql
Created May 16, 2020 13:20
Data from devstronomy's GitHub
CREATE DATABASE devstronomy;
DROP TABLE IF EXISTS devstronomy.`planet`;
CREATE TABLE devstronomy.`planet` (
`name` varchar(255) NOT NULL,
`mass` decimal(16,6) NOT NULL,
`diameter` decimal(16,6) NOT NULL,
`density` decimal(16,6) NOT NULL,
kovid-r / devstronomy_window_fn_doesnt_work_with_agg.sql
Last active May 17, 2020 12:49
Window functions don't work with aggregates
select max(p.mass) max_mass,
max(p.diameter) max_diameter,
max(p.density) max_density,
last_value( over (partition by null order by p.mass asc) lightest_planet,
from devstronomy.planet as p;
kovid-r / devstronomy_sequence_window_functions.sql
Created May 17, 2020 14:39
Sequence Window Functions Example
row_number() over (partition by order by sat_rownum_by_sat_mass,
rank() over (partition by order by sat_rank_by_sat_mass,
dense_rank() over (partition by order by sat_dense_rank_by_sat_mass
from devstronomy.satellite as s right join
devstronomy.planet as p
on = s.planet_id
where = 'Jupiter';
row_number() over () rn,
rank() over () rnk,
dense_rank() over () dns_rnk,
lead( over () lead_name,
lag( over () lag_name,
first_value( over () fv_name,
last_value( over () lv_name,
ntile(4) over () quartile,
ntile(5) over () quintile
kovid-r / devstronomy_last_value_example.sql
Created May 17, 2020 15:30
LAST_VALUE doesn't work without specifying windows
select distinct,
first_value( over (partition by order by desc, s.radius desc) biggest_satellite,
last_value( over (partition by order by desc, s.radius desc) smallest_satellite
from devstronomy.planet as p
left join devstronomy.satellite as s
on = s.planet_id;
kovid-r / devstronomy_small_ddl.sql
Created May 18, 2020 09:14
Subset of Columns from Devstronomy's Dataset
CREATE TABLE `l_planet` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) CHARACTER SET utf8 NOT NULL,
`mass` decimal(16,6) NOT NULL,
`diameter` decimal(16,6) NOT NULL,
`density` decimal(16,6) NOT NULL,
`gravity` decimal(16,6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `l_satellite` (
kovid-r / devstronomy_random_satellite_random_planet.sql
Created May 18, 2020 09:20
Random Satellite of a Random Planet
select *
from devstronomy.l_satellite
where planet_id = (select id
from devstronomy.l_planet
order by rand()
limit 1)
order by rand()
limit 1;
kovid-r / devstronomy_random_planet_random_satellite_non_repeat.sql
Last active May 18, 2020 10:53
Select Non-repeating Planet/Satellite Combinations
create table devstronomy.temp_l_satellite like devstronomy.l_satellite;
alter table devstronomy.temp_l_satellite add primary key (id);
alter table devstronomy.temp_l_satellite add column ins_ts timestamp default current_timestamp not null;
insert ignore into devstronomy.temp_l_satellite
select ls.*, current_timestamp
from devstronomy.l_satellite ls
where planet_id = (select
from devstronomy.l_planet lp
order by rand()
kovid-r / devstronomy_generate_random_test_data.sql
Last active May 18, 2020 11:09
Random test data generation Devstronomy
select distinct *
from (select *
from devstronomy.l_satellite
where rand() < .5
union all
select *
from devstronomy.l_satellite
where rand() > .5
) t
order by rand()
kovid-r / cast_ex_special_case_example.sql
Last active May 19, 2020 12:31
Special Case Example for Casting
select a, b, a = b
from (
select 13 a, '13dfgs' b union all
select 13 a, '130dfgs' b union all
select 13 a, '13' b union all
select 13 a, '0013dfgs' b union all
select 13 a, ' 13dfgs' b union all
select 13 a, 'dfgs13'
) as t;