select album_info.album_id,album_info.album_name,count(1) as listener_count from album_info
left join song_info on album_info.album_id = song_info.album_id
left join listen_rcd on listen_rcd.song_id = song_info.song_id
left join singer_info on singer_info.singer_id = song_info.origin_singer_id
where singer_info.singer_name='周杰伦'
and listen_rcd.if_finished = 1
group by album_info.album_id,album_info.album_name
order by listener_count
limit 1

select song_name , 
 CONCAT(song_name, '-', album_name) 
as song_album
from song_info 
left join album_info on song_info.album_id = album_info.album_id

select user_id,DAYNAME(start_time) as day_of_week,count(1)
from listen_rcd 
group by user_id,DAYNAME(start_time)
order by user_id,DAYNAME(start_time) asc

select listen_rcd.id,listen_rcd.user_id,listen_rcd.start_time,listen_rcd.song_id,listen_rcd.if_finished from singer_info 
left join song_info on singer_info.singer_id = song_info.origin_singer_id
left join listen_rcd on listen_rcd.song_id = song_info.song_id
where song_info.song_id=13
and listen_rcd.if_finished = 1
and date(listen_rcd.start_time) between '2023-12-10'and '2023-12-31' 
order by start_time

select listen_rcd.id,listen_rcd.user_id,listen_rcd.start_time,listen_rcd.song_id,listen_rcd.user_id from singer_info 
left join song_info on singer_info.singer_id = song_info.origin_singer_id
left join listen_rcd on listen_rcd.song_id = song_info.song_id
where song_info.song_id=13
and listen_rcd.if_finished = 1
and date(listen_rcd.start_time) between '2023-12-10'and '2023-12-31' 
order by start_time

select listen_rcd.id,listen_rcd.user_id,listen_rcd.start_time,listen_rcd.song_id,listen_rcd.user_id from singer_info 
left join song_info on singer_info.singer_id = song_info.origin_singer_id
left join listen_rcd on listen_rcd.song_id = song_info.song_id
where song_info.song_id=13
and listen_rcd.if_finished = 1
and date(listen_rcd.start_time) between '2023-12-10'and '2023-12-31'

select 
year(list_date) as Y,
sum(case when name rlike '医|药|生物' then1 else 0 end ) as cnt
from stock_info 
where year(list_date) between 2020 and 2024
group by Y
order by Y

select * from stock_info 
where year(list_date) between 2020 and 2024
 and name rlike '医|药|生物'

select * from stock_info
where date(list_date) >='2006-06-01'
and date(list_date) <='2006-09-01'
and name like '%银行%'

select 
    * 
from 
    stock_info 
where 
     industry='银行'
 andyear(list_date)>=2000
order by list_date

select 
    * 
from 
    stock_info 
where 
    area = '北京'
    and industry='银行'
 andyear(list_date)>=2000
order by list_date

select * from stock_info 
wherearea='北京'
and name like '%银行%'
order by list_date

select industry,AVG(length(name)) AS avg_name_length,
MAX(length(name)) AS max_name_length,
MIN(length(name)) AS min_name_length
from stock_info 
WHERE industry IN('通信设备','生物制药','旅游景点','医疗保健','食品')
group by industry

with industry_avg_total as 
(
select 
industry,
sum(DATEDIFF(CURRENT_DATE(), list_date))/count(1) as avg_age_days,
sum(DATEDIFF(CURRENT_DATE(), list_date)) as age_days,
count(1) as total_companies
from stock_info 
group by industry
)
,
 industry_over_20 as 
(
select 
industry,
	count(1) as total_companies 
from stock_info 
where DATEDIFF(CURRENT_DATE(), list_date) >=(365*20)
group by industry
)
select industry_avg_total.industry,
	industry_avg_total.avg_age_days,
	industry_over_20.total_companies as over_20_years,
industry_avg_total.total_companies,
industry_over_20.total_companies/industry_avg_total.total_companies as proportion_over_20_years
from industry_avg_total
left join industry_over_20 on industry_avg_total.industry = industry_over_20.industry
order by avg_age_days desc

select 
	area,
	count(1) as total_companies,
sum(case when name rlike '中国|^中' then 1 else 0 end) as chinese_named_companies,
ROUND(SUM(CASE WHEN name rlike '中国|^中' THEN 1 ELSE 0 END) / COUNT(*),3) AS proportion
from stock_info 
group by area
order by proportion desc
limit 5

select 
	area,
	count(1) as total_companies,
sum(case when area rlike '中国|^中' then 1 else 0 end) as chinese_named_companies,
ROUND(SUM(CASE WHEN name rlike '中国|^中' THEN 1 ELSE 0 END) / COUNT(*),3) AS proportion
from stock_info 
group by area
order by proportion desc
limit 5

selectts_code,
    symbol,
    name,
    area,
    industry,
    list_date
from
(
 select stock_info.* ,
row_number()over(partition by industry ORDER BY list_date ASC ) as Sort
from stock_info 
where name rlike '^中国|中' 
)as table1 where Sort = 1

select count(*) from stock_info 
where industry in('通信设备','生物制药','旅游景点
','医疗保健','食品')
and name rlike'华|中'

with min_listdateas
(
select industry, min(list_date) as min_list_date
from stock_info 
group by industry
)
select stock_info.* 
	from stock_info
inner join min_listdate
onstock_info.industry = min_listdate.industry
	and	stock_info.list_date = min_listdate.min_list_date
order by stock_info.list_date asc

with min_listdateas
(
select industry, min(list_date) as min_list_date
from stock_info 
group by industry
)
select stock_info.* 
	from stock_info
rightjoin min_listdate
onstock_info.industry = min_listdate.industry
	and	stock_info.list_date = min_listdate.min_list_date
order by stock_info.list_date asc