Skip to content

Instantly share code, notes, and snippets.

Working from home

Kovid Rathee kovid-r

Working from home
View GitHub Profile
kovid-r / generate_test_data_example_1.sql
Last active September 16, 2020 14:47
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 / devstronomy_data.sql
Created May 16, 2020 13:20
Data from devstronomy's GitHub
View devstronomy_data.sql
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
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( 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
View devstronomy_sequence_window_functions.sql
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';
View devstronomy_basic_window_functions.sql
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
View devstronomy_last_value_example.sql
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
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` (
kovid-r / devstronomy_random_satellite_random_planet.sql
Created May 18, 2020 09:20
Random Satellite of a Random Planet
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 / devstronomy_random_planet_random_satellite_non_repeat.sql
Last active May 18, 2020 10:53
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
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
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()