query

See stock and performance based on Stock Price Range：

SELECT '0_to_5_price',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price BETWEEN 0 AND 5

union

SELECT '5_to_10_price',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price BETWEEN 5 AND 10

union

SELECT '10_to_20_price',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price BETWEEN 10 AND 20

union

SELECT '20_to_40_price',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price BETWEEN 20 AND 40

union

SELECT '40_to_60_price',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price BETWEEN 40 AND 60

union 

SELECT '60_to_100_price',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price BETWEEN 60 AND 100

union

SELECT '100_to_200_price',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price BETWEEN 100 AND 200

union

SELECT 'over 200',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where price > 200


See stock performance based on market_cap：
SELECT '小于1亿',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where market_cap < 100000000

union

SELECT '一亿到10亿',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where market_cap > 100000000 and market_cap < 1000000000


union

SELECT '10亿到30亿',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where market_cap > 1000000000 and market_cap < 3000000000

union

SELECT '30亿到50亿',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where market_cap > 1000000000 and market_cap < 5000000000

union

SELECT '50亿到100亿',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where market_cap > 5000000000 and market_cap < 10000000000

union 

SELECT '大于100亿',AVG(change_rate) as daily_rate,AVG(perf_week) as week_performance from get_last_ticker_data_with_profile where market_cap > 10000000000


see the option movement data
select
  ticker,
  type,
  count(*) cnt_total,
  sum(
    case when bid_status = 'same' then 1 else 0 end
  ) cnt_bid_same,
  sum(
    case when bid_status = 'increaed' then 1 else 0 end
  ) cnt_bid_increaed,
  sum(
    case when bid_status = 'decreaed' then 1 else 0 end
  ) cnt_bid_decreaed,
  sum(
    case when vol_status = 'same' then 1 else 0 end
  ) cnt_vol_same,
  sum(
    case when vol_status = 'increaed' then 1 else 0 end
  ) cnt_vol_increaed,
  sum(
    case when vol_status = 'decreaed' then 1 else 0 end
  ) cnt_vol_decreaed,
  sum(
    case when interest_status = 'same' then 1 else 0 end
  ) cnt_interest_same,
  sum(
    case when interest_status = 'increaed' then 1 else 0 end
  ) cnt_interest_increaed,
  sum(
    case when interest_status = 'decreaed' then 1 else 0 end
  ) cnt_interest_decreaed
from
  (
    select
      b.ticker,
      b.type,
      b.contract_name,
      c.bid - b.bid bid_diff,
      c.volume - b.volume volume_diff,
      c.open_interest - b.open_interest interest_diff,
      case when c.bid - b.bid > 0 then 'increaed' when c.bid - b.bid < 0 then 'decreaed' else 'same' end bid_status,
      case when c.volume - b.volume > 0 then 'increaed' when c.volume - b.volume < 0 then 'decreaed' else 'same' end vol_status,
      case when c.open_interest - b.open_interest > 0 then 'increaed' when c.open_interest - b.open_interest < 0 then 'decreaed' else 'same' end interest_status
    from
      (
        select
          ticker,
          type,
          contract_name,
          min(created_on) min_created,
          max(created_on) max_created
        from
          ticker_options
        group by
          ticker,
          type,
          contract_name
      ) a
      inner join ticker_options b on a.ticker = b.ticker
      and a.type = b.type
      and a.contract_name = b.contract_name
      and a.min_created = b.created_on
      inner join ticker_options c on a.ticker = b.ticker
      and a.type = c.type
      and a.contract_name = c.contract_name
      and a.max_created = c.created_on
  ) a
group by
  ticker,
  type
having cnt_interest_increaed > 35 and cnt_bid_increaed > 0 and cnt_bid_increaed-cnt_bid_decreaed>0 and cnt_vol_increaed > cnt_vol_decreaed（对于最后二个限定条件不那么严格可以去掉，如果不严格可以对cnt_interest_increaed进行更严格的限定）


#see emotion：
select a.*,b.price,b.change_rate,b.industry from (select * from mega_grid_sortbycount where total_count > 5) a inner join get_last_ticker_data_with_profile b on a.ticker = b.ticker


see insider trading info:
SELECT 'insider sales',sum(num_shares)
FROM insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 5 DAY) as date )

union 

select 'insider buy',sum(num_shares)
FROM insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 5 DAY) as date )

union
select 'insider option exercise',sum(num_shares)
FROM insider_trading_info where transaction = 'option exercise' and date >= cast( date_sub(NOW(), INTERVAL 5 DAY) as date )

see insider buy ratio by day
select CASE WHEN b.sum = 0 Then "NO INSIDER BUY" ELSE (a.sum/b.sum) END as sale_buy_ratio from (SELECT sum(REPLACE(num_shares, ',', '')) as sum from insider_trading_info where date = '2021-03-31' and transaction = 'sale') a,(SELECT sum(REPLACE(num_shares, ',', '')) as sum from insider_trading_info where date = '2021-03-31' and transaction = 'buy') b

see insider buy ratio sum：
SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 3 DAY) as date ))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 5 DAY) as date ))b

see ratio on different days:
select '3days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 3 DAY) as date ))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 3 DAY) as date ))b) as 3_day_salebuy_ratio

union

select '5days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 5 DAY) as date ))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 5 DAY) as date ))b) as 5_day_salebuy_ratio

Union

select '10days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 10 DAY) as date ))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 10 DAY) as date ))b) as 10_day_salebuy_ratio

Union

select '30days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 30 DAY) as date ))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 30 DAY) as date ))b) as 30_day_salebuy_ratio

Union
select '45days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 45 DAY) as date ))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 45 DAY) as date ))b) as 45_day_salebuy_ratio


#See ratio by week
select '7days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 7 DAY) as date ))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 7 DAY) as date ))b) as 7_day_salebuy_ratio
union
select '14days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 14 DAY) as date ) and date <= cast( date_sub(NOW(), INTERVAL 7 DAY) as date))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 14 DAY) as date) and date <= cast( date_sub(NOW(), INTERVAL 7 DAY) as date))b) as 14_day_salebuy_ratio
union
select '21days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 21 DAY) as date ) and date <= cast( date_sub(NOW(), INTERVAL 14 DAY) as date))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 21 DAY) as date) and date <= cast( date_sub(NOW(), INTERVAL 14 DAY) as date))b) as 21_day_salebuy_ratio
union
select '28days',(SELECT (a.countA/b.countB)
FROM (select sum(num_shares) as countA from insider_trading_info where transaction = 'sale' and date >= cast( date_sub(NOW(), INTERVAL 28 DAY) as date ) and date <= cast( date_sub(NOW(), INTERVAL 21 DAY) as date))a,(select sum(num_shares) as countB from insider_trading_info where transaction = 'buy' and date >= cast( date_sub(NOW(), INTERVAL 28 DAY) as date) and date <= cast( date_sub(NOW(), INTERVAL 21 DAY) as date))b) as 28_day_salebuy_ratio



see industry of insider buy
select a.*,b.industry from (select ticker,insider,relationship,date,transaction,cost,value,num_shares from insider_trading_info where date >= cast( date_sub(NOW(), INTERVAL 3 DAY) as date )) a inner join get_last_ticker_data_with_profile b on a.ticker = b.ticker order by transaction desc

Industry query:
select b.industry,sum(REPLACE(a.num_shares, ',', '')) as sum  ,a.transaction from (select ticker,insider,relationship,date,transaction,cost,value,num_shares from insider_trading_info where date >= cast( date_sub(NOW(), INTERVAL 5 DAY) as date )) a inner join get_last_ticker_data_with_profile b on a.ticker = b.ticker group by a.transaction,b.industry

See spy sector performance
select ticker, company,change_rate
,perf_week
,perf_month,perf_quarter
,perf_half_y,perf_year
,prev_close,price, cast(price-prev_close as decimal(10, 2)) as price_compared_yes_close ,volume,SUBSTR(avg_volume,1,LENGTH(avg_volume)-1)*1000000 as average_volumn,volume-SUBSTR(avg_volume,1,LENGTH(avg_volume)-1)*1000000 as volume_differ,dividend,dividend_rate
,weekly_volatility,monthly_volatility,sma20,sma50,sma200
 from get_last_ticker_data_with_profile where ticker in ("spy","xlb","xlc","xle","xlf","xli","xlk","xlp","xlre","xlu","xlv","xly") 




#Evaluate institution cost
SELECT (SUM(Replace(value,',',''))/SUM(Replace(shares,',',''))) AS newvalue FROM ticker_holder_info_1 WHERE ticker ="jfin" or ticker = ‘appl’ (ticker可以换)

#Evaluate Institution add stock/reduce stock by ratio
select a.share_increase/abs(b.share_decrease) as share_diff from (SELECT sum(shares_diff) As share_increase FROM ticker_holder_shares_change where ticker = 'DQ' and day_recorded >= '2020-10-30' and type in ( 'increased', 'added new' )) AS a,(SELECT sum(shares_diff) As share_decrease FROM ticker_holder_shares_change where ticker = 'DQ' and day_recorded >= '2020-10-30' and type in ( 'decreased', 'quit' )) As b


#Evaluate Institution add stock/reduce stock by real number
select (a.share_increase--b.share_decrease) as share_diff from (SELECT sum(shares_diff) As share_increase FROM ticker_holder_shares_change where ticker = 'DQ' and day_recorded >= '2020-10-30' and type in ( 'increased', 'added new' )) AS a,(SELECT sum(shares_diff) As share_decrease FROM ticker_holder_shares_change where ticker = 'DQ' and day_recorded >= '2020-10-30' and type in ( 'decreased', 'quit' )) As b
 (标注：时间以及股票名字可以替换)




#When mega table is not working:

select a.*,industry from ticker_linear_regression_stat a left join  get_last_ticker_data_with_profile b on a.ticker = b.ticker where a.ticker in (select ticker from mega_grid_sortbycount where total_count>7) and mid_inst_own_slope > 0 and mid_sale_slope > 0


select a.*,industry from ticker_linear_regression_stat a left join  get_last_ticker_data_with_profile b on a.ticker = b.ticker where a.ticker in (select ticker from mega_grid_sortbycount where total_count>6) and mid_inst_own_slope > 0 and mid_sale_slope > 0 and short_eps_next_y_slope > 3 and short_income_slope>50000 and mid_income_slope > 0 order by short_inst_own_slope desc limit 20



#calculate the increase from institution cost
SELECT (SUM(Replace(value,',',''))/SUM(Replace(shares,',',''))) AS newvalue FROM ticker_holder_info_1 WHERE ticker ="jfin"  or ticker = ‘appl’

#bio industry
select AVG(change_rate) as daily_rate, STDDEV(change_rate) as daily_std,AVG(perf_week) as avg_week_perf,AVG(perf_month) as monty_rate  from get_last_ticker_data_with_profile where industry = "Biotechnology" or industry = "Diagnostics & Research" or industry = "Medical Instruments & Supplies" or industry = "Medical Care Facilities" and price < 10


#travel industry
select * from mega_grid where ticker in (select ticker from ticker_profile where instr( description, 'travel' ) > 0 or instr( description, 'hotel' )) and not industry = "Banks - Regional" and not ticker in ('GOOGL','DOYU','KHC') and not instr(industry,'insurance')>0  and instr( short_inst_own, 'up' ) > 0 and instr( mid_sale, 'up' ) > 0 limit 50

#bid/ask data
select ticker,type,sum(bid) as sum_bid, sum(ask) as sum_ask,sum(volume) as sum_volume, sum(open_interest) as sum_open_interest,avg(implied_volatility) as avg_volatility from ticker_options group by ticker,type having  avg_volatility <0.05 and avg_volatility > 0 limit 100



choose option for specific stock：
select * from ticker_options where ticker = "Qdel" and bid/ask>0.7 Group by ticker,type,contract_name having max(volume)>10 order by volume desc,open_interest desc,implied_volatility asc   (Qdel可以代替)


#See bond:
select * from class_4.ticker_hist where ticker in ("^FVX","^IRX","^TNX", "^TYX") and date >= cast( date_sub(NOW(), INTERVAL 4 DAY) as date )

#see report on different days
select ticker,date,price,change_rate,cast(price-prev_close as decimal(10, 2)) as price_compared_yes_close,volume,cast(SUBSTR(avg_volume,1,LENGTH(avg_volume)-1)*1000000 as decimal(10, 2)) as average_volumn,volume-SUBSTR(avg_volume,1,LENGTH(avg_volume)-1)*1000000 as volume_differ,perf_week,perf_month,perf_quarter,perf_half_y,perf_year,dividend,dividend_rate,sma20,sma50,sma200 from us_tickers where date >= cast( date_sub(NOW(), INTERVAL 10 DAY) as date ) and ticker in ("spy","xlb","xlc","xle","xlf","xli","xlk","xlp","xlre","xlu","xlv","xly")




#see successful insider
select 
a.*,g.price, inst_cost,sum_shares_increased,sum_shares_decreased,short_short_ratio_slope,cast((g.price-a.cost)/abs(a.cost)*100 as decimal(10, 2)) as cost_diff_percent,g.target_price,(g.price-g.target_price) as to_target_diff,g.peg,b.close,c.date date_followed, c.close close_followed,
datediff( c.date, a.date ) date_diff, g.industry,e.total_count,e.good_factors,concat( xxx_call.cnt_total,'::',xxx_put.cnt_total ) call_puts_total,
concat( xxx_call.cnt_bid_increased,'::',xxx_call.cnt_bid_decreased ) calls_bid_inc_dec_cnts,
concat(xxx_call.cnt_vol_increased,'::', xxx_call.cnt_vol_decreased ) calls_vol_inc_dec_cnts,
concat( xxx_put.cnt_bid_increased, '::', xxx_put.cnt_bid_decreased ) puts_bid_inc_dec_cnts,
concat( xxx_put.cnt_vol_increased,'::',xxx_put.cnt_vol_decreased ) puts_vol_inc_dec_cnts,
concat( cast( ( c.close - b.close ) * 100 / b.close as decimal( 20,2 ) ), '$$$percent_sign$$$' ) close_price_diff,

concat( cast( ( d.high - b.close ) * 100 / b.close as decimal( 20,2 ) ), '$$$percent_sign$$$' )  high_price_diff,

case 
when cast( ( d.high - b.close ) * 100 / b.close as decimal( 20,2 ) ) >= 30 then '!!! 30$$$percent_sign$$$ win !!!'
when cast( ( d.high - b.close ) * 100 / b.close as decimal( 20,2 ) ) >= 20 then '!!! 20$$$percent_sign$$$ win !!!'
when cast( ( d.high - b.close ) * 100 / b.close as decimal( 20,2 ) ) >= 10 then '!!! 10$$$percent_sign$$$ win !!!'
when cast( ( d.high - b.close ) * 100 / b.close as decimal( 20,2 ) ) >= 5 then '!!! 5$$$percent_sign$$$ win !!!'
else ''
end winning_flag


from
(
select  

insider, ticker, cost, relationship,date,num_shares,value
from
insider_trading_info
where transaction = 'buy'
and insider = 'Karavatakis Phyllis Q.' or insider = 'Adams Arthur Loran' or insider = 'Ferro Kevin' or insider = 'Gunning Patrick J.' or insider = 'Choi Brian J' or insider = 'Nelson Ann C' or insider = 'MAISEL DAVID' or insider = 'FELDMANN GREGORY W' or insider = 'GREENBERG WILLIAM ROSS' or insider='Van Dyke Litz H' or insider='KATZ A. AKIVA' or insider='BIRD MICHAEL R' or insider= 'Gerspach John C' or insider='Nelson Ann C' or insider = 'DeVries James David' or insider = 'EcoR1 Capital, LLC' or insider = 'LEE SANG YOUNG'
) a
inner join class_4.ticker_hist b
on a.ticker = b.ticker and a.date = b.date

inner join class_4.ticker_hist c
on a.ticker = c.ticker and datediff( c.date,a.date ) between 0 and 10 

inner join class_4.ticker_hist d
on a.ticker = d.ticker and d.date = c.date
inner join get_last_ticker_data_with_profile g on a.ticker = g.ticker
left join ticker_options_summary xxx_call
on a.ticker = xxx_call.ticker and xxx_call.type = 'call'
left join ticker_options_summary xxx_put
on a.ticker = xxx_put.ticker and xxx_put.type = 'put'
left join mega_grid_sortbycount e
on a.ticker = e.ticker
left join ticker_linear_regression_stat ee
on a.ticker = ee.ticker
left join (SELECT 
ticker, sum( shares_diff ) sum_shares_decreased
FROM test.ticker_holder_shares_change 
where day_recorded > '2020-12-30' 
and type in ( 'decreased', 'quit' )
GROUP BY ticker ) ss on a.ticker = ss.ticker
left join (SELECT 
ticker, sum( shares_diff ) sum_shares_increased
FROM test.ticker_holder_shares_change 
where day_recorded > '2020-12-30' 
and type in ('increased', 'added new')
GROUP BY ticker) bb on a.ticker = bb.ticker
left join (
SELECT ticker,(SUM(Replace(value,',',''))/SUM(Replace(shares,',',''))) AS inst_cost FROM ticker_holder_info_1 group by ticker) ii on a.ticker = ii.ticker
order by date desc  



#see good stock option data
select a.ticker,a.total_count,b.industry,concat( xxx_call.cnt_total,'::',xxx_put.cnt_total ) call_puts_total,
concat( xxx_call.cnt_bid_increased,'::',xxx_call.cnt_bid_decreased ) calls_bid_inc_dec_cnts,
concat(xxx_call.cnt_vol_increased,'::', xxx_call.cnt_vol_decreased ) calls_vol_inc_dec_cnts,
concat( xxx_put.cnt_bid_increased, '::', xxx_put.cnt_bid_decreased ) puts_bid_inc_dec_cnts,
concat( xxx_put.cnt_vol_increased,'::',xxx_put.cnt_vol_decreased ) puts_vol_inc_dec_cnts from (select ticker,total_count from mega_grid_sortbycount where total_count>7) a left join get_last_ticker_data_with_profile b on a.ticker = b.ticker left join ticker_options_summary xxx_call
on a.ticker = xxx_call.ticker and xxx_call.type = 'call'
left join ticker_options_summary xxx_put
on a.ticker = xxx_put.ticker and xxx_put.type = 'put'


#improved version
select a.ticker,a.total_count,b.industry,b.price,b.target_price,CASE WHEN b.market_cap IS NULL Then "NULL VALUE" ELSE concat(b.market_cap/1000000,'M') End as market_cap,inst_cost, sum_shares_increased, sum_shares_decreased,concat( xxx_call.cnt_total,'::',xxx_put.cnt_total ) call_puts_total,
concat( xxx_call.cnt_bid_increased,'::',xxx_call.cnt_bid_decreased ) calls_bid_inc_dec_cnts,
concat(xxx_call.cnt_vol_increased,'::', xxx_call.cnt_vol_decreased ) calls_vol_inc_dec_cnts,
concat( xxx_put.cnt_bid_increased, '::', xxx_put.cnt_bid_decreased ) puts_bid_inc_dec_cnts,
concat( xxx_put.cnt_vol_increased,'::',xxx_put.cnt_vol_decreased ) puts_vol_inc_dec_cnts from (select ticker,total_count from mega_grid_sortbycount where total_count>=6) a left join (select * from get_last_ticker_data_with_profile where price < target_price and market_cap > 150000000) b on a.ticker = b.ticker left join (SELECT 
ticker, sum( shares_diff ) sum_shares_increased
FROM test.ticker_holder_shares_change 
where day_recorded > '2020-12-30' 
and type in ('increased', 'added new')
GROUP BY ticker) bb on a.ticker = bb.ticker
left join (SELECT 
ticker, sum( shares_diff ) sum_shares_decreased
FROM test.ticker_holder_shares_change 
where day_recorded > '2020-12-30' 
and type in ( 'decreased', 'quit' )
GROUP BY ticker ) ww on a.ticker = ww.ticker
left join (SELECT ticker,(SUM(Replace(value,',',''))/SUM(Replace(shares,',',''))) AS inst_cost FROM ticker_holder_info_1 group by ticker) ii on a.ticker = ii.ticker
left join ticker_options_summary xxx_call
on a.ticker = xxx_call.ticker and xxx_call.type = 'call'
left join ticker_options_summary xxx_put
on a.ticker = xxx_put.ticker and xxx_put.type = 'put' where xxx_call.cnt_bid_increased > xxx_call.cnt_bid_decreased（在b table中加入了market_cap的query控制风险）

select a.*,b.industry from (SELECT * from insider_trading_info where date = '2021-03-31') a left join get_last_ticker_data_with_profile b on a.ticker = b.ticker order by transaction



#see institution stock：
select a.ticker,c.price, c.target_price, (c.target_price-c.price) as price_diff,sum_shares_decreased,sum_shares_increased,concat( xxx_call.cnt_bid_increased,'::',xxx_call.cnt_bid_decreased ) calls_bid_inc_dec_cnts,
concat(xxx_call.cnt_vol_increased,'::', xxx_call.cnt_vol_decreased ) calls_vol_inc_dec_cnts,
concat( xxx_put.cnt_bid_increased, '::', xxx_put.cnt_bid_decreased ) puts_bid_inc_dec_cnts,
concat( xxx_put.cnt_vol_increased,'::',xxx_put.cnt_vol_decreased ) puts_vol_inc_dec_cnts
,e.insider_buy from (SELECT 
ticker, sum( shares_diff ) sum_shares_decreased
FROM test.ticker_holder_shares_change 
where day_recorded > '2020-12-30' 
and type in ( 'decreased', 'quit' )
GROUP BY ticker
having abs(sum_shares_decreased) < 700) a
inner join (SELECT 
ticker, sum( shares_diff ) sum_shares_increased
FROM test.ticker_holder_shares_change 
where day_recorded > '2020-12-30' 
and type in ('increased', 'added new')
GROUP BY ticker
having abs(sum_shares_increased)> 20000) b
on a.ticker = b.ticker
left join (select * from get_last_ticker_data_with_profile where target_price> price) c on a.ticker = c.ticker 
left join (select ticker,sum(num_shares) as insider_buy, date from insider_trading_info where transaction = 'buy' and date > '2020-12-30'  group by ticker ) e on a.ticker = e.ticker
left join (select ticker,sum(num_shares) as insider_sell, date from insider_trading_info where transaction = 'sale' and date > '2020-12-30'  group by ticker ) yy on a.ticker = e.ticker
left join ticker_options_summary xxx_call
on a.ticker = xxx_call.ticker and xxx_call.type = 'call'
left join ticker_options_summary xxx_put
on a.ticker = xxx_put.ticker and xxx_put.type = 'put'


#see SMA：
select *, avg(close) OVER(ORDER BY date
     ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) 
     as sma5, avg(close) OVER(ORDER BY date
     ROWS BETWEEN 9 PRECEDING AND CURRENT ROW ) 
     as sma10,avg(close) OVER(ORDER BY date
     ROWS BETWEEN 19 PRECEDING AND CURRENT ROW ) 
     as sma20,avg(close) OVER(ORDER BY date
     ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) 
     as sma30
 from class_4.ticker_hist where ticker = "any ticker"
下一个任务：大盘指数：spy，nasdaq的机器学习用来预测 （预测效果不太好，需要优化）
下一个任务：分析earning call （已经完成earning date爬取）
下一个任务：查看twitter股票情绪面分析  （已经完成初步）
下一个任务：爬取linkedin公司员工数据 （未完成）