SELECT name, subject, class_code, graduate_date
FROM teachers
WHERE graduate_date BETWEEN '1994-01-01' AND '1997-12-31' AND gender = 'f'
ORDER BY graduate_date ASC;
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
order by id;
WITH data AS (
SELECT
DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE
login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY usr_id, login_date
),
lead_data AS (
SELECT
usr_id,
login_date,
lead(login_date, 1) OVER (PARTITION BY usr_id ORDER BY login_date) AS lead_date
FROM data
),
retention_data AS (
SELECT
usr_id,
login_date,
CASE
WHEN lead_date IS NULL THEN 0
WHEN DATEDIFF(lead_date, login_date) = 1 THEN 1
ELSE 0
END AS is_login_next_date
FROM lead_data
)
SELECT
login_date AS first_login_date,
CONCAT(ROUND(AVG(is_login_next_date) * 100, 2), '%') AS T1_retention_rate
FROM retention_data
GROUP BY login_date
ORDER BY login_date;
with data as (
SELECT
DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE
DATEDIFF(CURDATE(), DATE(login_time)) <= 30
order by usr_id,login_date
),
lead_data as (
select
usr_id,
login_date,
lead(login_date,1) over (partition by usr_id order by login_date) as lead_date
FROM data
)
select
login_date as first_login_date,
concat(round(avg(lead_date is not null)*100, 2), '%') as T1_retention_rate
from
lead_data
group by
login_date
order by
login_date;
with data1 as (
select distinct
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
),
data2 as (
select
T.usr_id,
T.login_date as T_date,
T_1.login_date as T_1_date
from
data1 as T
left join
data1 as T_1
on
T.usr_id = T_1.usr_id
and datediff(T.login_date, T_1.login_date) = -1
)
select * from data2
SELECT name, subject, class_code, qualification
FROM teachers
WHERE (fir_degr = '北京大学' OR fir_degr = '清华大学') AND head_teacher IS NOT NULL
ORDER BY name ASC;
with the_result as (
select s1.student_id,
s1.subject,
s1.score,
s2.name
from scores s1
inner join
students s2
on s1.student_id = s2.student_id
where exam_date = '2024-06-30'
and subject = '数学'
)
select
case
when score >= 110 then '[110,120]'
when score >= 90 then '[90,110)'
when score >= 60 then '[60,90)'
ELSE '[0, 60)'
end as `score_range`,
count(student_id) as `num_students`
from the_result
group by score_range
order by score_range desc
with the_result as (
select s1.student_id,
s1.subject,
s1.score,
s2.name
from scores s1
inner join
students s2
on s1.student_id = s2.student_id
where exam_date = '2024-06-30'
and subject = '数学'
)
select
case
when score >= 110 then '[110,120)'
when score >= 90 then '[90,110)'
when score >= 60 then '[60,90)'
ELSE '[0, 60)'
end as `score_range`,
count(student_id) as `num_students`
from the_result
group by score_range
order by score_range desc
with the_result as (
select s1.student_id,
s1.subject,
s1.score,
s2.name
from scores s1
inner join
students s2
on s1.student_id = s2.student_id
where exam_date = '2024-06-30'
and subject = '数学'
)
select
case
when score >= 110 then '[110,120)'
when score >= 90 then '[90,110)'
when score >= 60 then '[60,90)'
ELSE '[0, 60)'
end as `score_range`,
count(student_id) as `num_students`
from the_result
group by score_range
order by score_range,num_students
with the_result as (
select s1.student_id,
s1.subject,
s1.score,
s2.name
from scores s1
inner join
students s2
on s1.student_id = s2.student_id
where exam_date = '2024-06-30'
and subject = '数学'
)
select
case
when score >= 110 then '[110,120)'
when score >= 90 then '[90,120)'
when score >= 60 then '[60,90)'
ELSE '[0, 60)'
end as `score_range`,
count(student_id) as `num_students`
from the_result
group by score_range
with ranked_scores as (
select
s1.student_id,
s1.name,
s2.score,
s2.subject,
row_number()over(partition by s2.subject order by s2.score desc) as `ranking`
from students s1
join scores s2
on s1.student_id = s2.student_id
where s1.grade_code = 'S1'
)
select *
from ranked_scores
where
ranking <= 10
and subject = '物理'
ORDER BY
ranking,student_id
with ranked_scores as (
select
s1.student_id,
s1.name,
s2.score,
rank()over(partition by s2.subject order by s2.score desc) as `ranking`
from students s1
join scores s2
on s1.student_id = s2.student_id
where s1.grade_code = 'S1'
and s2.subject = '物理'
)
select *
from ranked_scores
where
ranking <= 10
ORDER BY
ranking,student_id
with ranked_scores as (
select
s1.student_id,
s1.name,
s2.score,
row_number()over(partition by s2.subject order by s2.score desc) as `rnk`
from students s1
join scores s2
on s1.student_id = s2.student_id
where s1.grade_code = 'S1'
and s2.subject = '物理'
)
select *
from ranked_scores
where
rnk <= 10
ORDER BY
rnk,student_id