Skip to content

Instantly share code, notes, and snippets.

Avatar
:octocat:
I may be slow to respond.

Kovid Rathee kovid-r

:octocat:
I may be slow to respond.
View GitHub Profile
@kovid-r
kovid-r / generate_test_data_example_1.sql
Last active Sep 16, 2020
Generate test data using MariaDB/MySQL
View generate_test_data_example_1.sql
with recursive series as (
select 1 as id union all
select id + 1 as id
from series
where id < 100),
cities as (select 'Santa Clara' city union all
select 'Los Angeles' union all
select 'Santa Clarita' union all
select 'San Bernardino' union all
select 'Alameda' union all
@kovid-r
kovid-r / devstronomy_data.sql
Created May 16, 2020
Data from devstronomy's GitHub
View devstronomy_data.sql
CREATE DATABASE devstronomy;
DROP TABLE IF EXISTS devstronomy.`planet`;
CREATE TABLE devstronomy.`planet` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`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
kovid-r / devstronomy_window_fn_doesnt_work_with_agg.sql
Last active May 17, 2020
Window functions don't work with aggregates
View devstronomy_window_fn_doesnt_work_with_agg.sql
select max(p.mass) max_mass,
max(p.diameter) max_diameter,
max(p.density) max_density,
last_value(p.name) over (partition by null order by p.mass asc) lightest_planet,
p.name
from devstronomy.planet as p;
View devstronomy_sequence_window_functions.sql
select p.name,
s.name,
s.gm,
row_number() over (partition by p.id order by s.gm) sat_rownum_by_sat_mass,
rank() over (partition by p.id order by s.gm) sat_rank_by_sat_mass,
dense_rank() over (partition by p.id order by s.gm) sat_dense_rank_by_sat_mass
from devstronomy.satellite as s right join
devstronomy.planet as p
on p.id = s.planet_id
where p.name = 'Jupiter';
View devstronomy_basic_window_functions.sql
select p.name,
row_number() over () rn,
rank() over () rnk,
dense_rank() over () dns_rnk,
lead(p.name) over () lead_name,
lag(p.name) over () lag_name,
first_value(p.name) over () fv_name,
last_value(p.name) over () lv_name,
ntile(4) over () quartile,
ntile(5) over () quintile
@kovid-r
kovid-r / devstronomy_last_value_example.sql
Created May 17, 2020
LAST_VALUE doesn't work without specifying windows
View devstronomy_last_value_example.sql
select distinct p.name,
first_value(s.name) over (partition by p.id order by s.gm desc, s.radius desc) biggest_satellite,
last_value(s.name) over (partition by p.id order by s.gm desc, s.radius desc) smallest_satellite
from devstronomy.planet as p
left join devstronomy.satellite as s
on p.id = s.planet_id;
@kovid-r
kovid-r / devstronomy_small_ddl.sql
Created May 18, 2020
Subset of Columns from Devstronomy's Dataset
View devstronomy_small_ddl.sql
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` (
View devstronomy_random_satellite_random_planet.sql
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
kovid-r / devstronomy_random_planet_random_satellite_non_repeat.sql
Last active May 18, 2020
Select Non-repeating Planet/Satellite Combinations
View devstronomy_random_planet_random_satellite_non_repeat.sql
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 lp.id
from devstronomy.l_planet lp
order by rand()
@kovid-r
kovid-r / devstronomy_generate_random_test_data.sql
Last active May 18, 2020
Random test data generation Devstronomy
View devstronomy_generate_random_test_data.sql
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()