kovid-r

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

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

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;

kovid-r

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';

kovid-r

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

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

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

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

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

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()