\! echo 1
use rocket;
\! echo
\! echo 2
#show tables;
\! echo
\! echo 3
#describe vehicle;
#describe launch;
\! echo
\! echo 4
set @row=0;
set @rowy=-1;
#with xx(date, site, num) as (select date, launchsite, (@row:=@row+1) from launch order by launchsite,date), zz(date, site, num) as (select date, launchsite, (@rowy:=@rowy+1) from launch order by launchsite,date) select xx.site, xx.date, zz.date, xx.num, zz.num, datediff(zz.date,xx.date) from xx join zz on  xx.site=zz.site and xx.num=zz.num;

#
#with xx(date, site, num) as (select date, launchsite, (@row:=@row+1) from launch order by launchsite,date), zz(date, site, num) as (select date, launchsite, (@rowy:=@rowy+1) from launch order by launchsite,date), qq(site, xd, zd, dd) as (select xx.site, xx.date, zz.date, datediff(zz.date,xx.date) from xx join zz on  xx.site=zz.site and xx.num=zz.num), q4(site, mxs,cou) as (select site, max(dd), count(site) as ddd from qq group by site order by ddd) select * from q4 where mxs>5 order by site;
#with xx(date, site, num) as (select date, launchsite, (@row:=@row+1) from launch order by launchsite,date limit 3) select * from xx join xx as zz on  xx.site=zz.site;


set @row=0;
# This is a very complex version of using lags because I did not understand the partition by piece.#with xx(date, site, row, lagsite) as (select date, launchsite, (@row:=@row+1), lag(launchsite,1) over (partition by launchsite order by date) from launch order by date,launchsite) select site, lagsite, row, datediff(date, lag(date,1) over (order by date)) from xx where site=lagsite order by site; 
#select launchsite, date, datediff(date, lag(date,1) over (partition by launchsite order by date)) as diff from launch order by launchsite,date ;
#with aa(site, date, datelag) as (select launchsite, date, lag(date,1) over (partition by launchsite order by date) as datelag from launch) select site, date, datelag, datediff(date, datelag) from aa where datelag is not null order by site,date ;

#with aa(site, date, diff) as (select launchsite, date, datediff(date, lag(date,1) over (partition by launchsite order by date)) from launch order by launchsite,date) select site, max(diff) from aa where diff is not NULL group by site;
#select launchsite, date, datediff(date, lag(date,1) over (partition by launchsite order by date)) as diff from launch order by launchsite,date limit 3; 
#with aa(site, date, diff) as (select launchsite, date, datediff(date, lag(date,1) over (partition by launchsite order by date)) from launch order by launchsite,date limit 3) select * from aa where diff is not NULL;

#with aa(site, date, diff) as (select launchsite, date, datediff(date, lag(date,1) over (partition by launchsite order by date)) from launch order by launchsite,date) select site, max(diff) from aa where diff is not NULL group by site;

#select launchsite, date, lag(date,4) over (order by date) from launch limit 10;



set @psite='xgxgxg';
set @pdate=curdate();
#with aa(psite, site, pdate, date) as (select @psite psite, @psite:=launchsite, @pdate pdate, @pdate:=date from launch order by launchsite, date) select site, date, pdate, datediff(date, pdate) from aa where site=psite;

#with aa(psite, site, pdate, date) as (select @psite psite, @psite:=launchsite, @pdate pdate, @pdate:=date from launch order by launchsite, date), bb(site, diff) as (select site, datediff(date, pdate) from aa where site=psite) select site, max(diff) from bb group by site;

set @qq:=0;
set @rr:=0;
#with aa(cc) as (select count(*) from launch group by launchsite), bb(mm,nn,oo) as (select cc, @rr:=@rr+cc, @qq:=@qq+cc*cc from aa) select max(oo), max(oo)/(max(nn)*max(nn)) from bb;

#select count(*)*count(*) from launch;



#set @pname:='xxxx';
#set @rank:=1;
select launchsite, @rank:=if(@pname=launchsite, @rank+1, if(@pname:=launchsite,1,1)) from launch as ll, (select @pname:='yweruiyw') as pp, (select @rank:=1) as rr order by launchsite;

#select launchsite, rank() over (partition by launchsite order by date) from launch order by launchsite, date;