Compute churn metrics and rank top students

You have two tasks.

Part A (R): You receive a Customers CSV with columns: id (INT), signup_date (YYYY-MM-DD), last_active_date (YYYY-MM-DD), churned (0/1/NULL), pay (DECIMAL/NULL). Clean and compute metrics under these rules: (1) Drop rows where id is NULL/blank; (2) If pay is NULL, set pay = 0; (3) If churned is NULL but pay > 0, set churned = 0 (treat as active); (4) Define active_days = DATEDIFF(last_active_date, signup_date); if last_active_date < signup_date, set active_days = 0; (5) Treat last_active_date as the churn date when churned = 1. Produce: (a) the average active_days across all remaining customers; and (b) the average pay among customers with churned = 1. Return both rounded to 2 decimals and explain briefly how each rule is enforced in your code. Use idiomatic tidyverse R. Sample data (for format clarity):

Customers
+-----+-------------+------------------+---------+------+
| id | signup_date | last_active_date | churned | pay |
+-----+-------------+------------------+---------+------+
| 1 | 2025-01-10 | 2025-03-05 | 1 | 99 |
| 2 | 2025-02-01 | 2025-02-20 | NULL | 15 |
| 3 | 2025-01-15 | 2025-01-18 | 0 | NULL |
| NULL| 2025-02-10 | 2025-02-12 | 1 | 50 |
| 4 | 2025-02-11 | 2025-02-05 | 1 | 25 |
+-----+-------------+------------------+---------+------+

Part B (SQL, MySQL 8.0): Using the schema below, write ONE query that: (i) computes each student’s avg_score across all grades; (ii) excludes students with zero completed assignments; (iii) keeps only the top CEIL(N/2) students by avg_score where N is the number of students with at least one grade; break ties using student_id ASC; (iv) outputs student_id, name, avg_score (rounded to 1 decimal), and a dense_rank over the selected students ordered by avg_score DESC, student_id ASC; and (v) still works correctly as more students/grades are added. Schema:

Teachers(id PK, name, classroom)
Students(id PK, name, primary_teacher_id FK -> Teachers.id)
Assignments(id PK, teacher_id FK -> Teachers.id)
Grades(student_id FK -> Students.id, assignment_id FK -> Assignments.id, grade DECIMAL(4,1), PK(student_id, assignment_id))
Sample tables:
Teachers
+----+------------+----------+
| id | name | classroom|
+----+------------+----------+
| 4 | Mr. Feeny | 301 |
| 8 | Mr. Cooper | 260 |
+----+------------+----------+
Students
+----+---------+--------------------+
| id | name | primary_teacher_id |
+----+---------+--------------------+
| 1 | Bobby | 4 |
| 2 | Susie | 8 |
| 3 | Deborah | 8 |
| 9 | Bruce | NULL |
+----+---------+--------------------+
Assignments
+----+------------+
| id | teacher_id |
+----+------------+
| 1 | 4 |
| 2 | 8 |
+----+------------+
Grades
+------------+---------------+-------+
| student_id | assignment_id | grade |
+------------+---------------+-------+
| 1 | 1 | 100.0 |
| 1 | 2 | 50.0 |
| 2 | 1 | 100.0 |
| 2 | 2 | 100.0 |
| 3 | 1 | 40.0 |
| 3 | 2 | 8.0 |
| 9 | 1 | 65.0 |
| 9 | 2 | 65.0 |
+------------+---------------+-------+

Clean customer churn data and compute summary metrics

You are given a Customers table (PostgreSQL) that contains raw, partially-dirty subscription records. Each row describes one customer's signup date, last active date, churn flag, and amount paid.

Write a single SQL query that first cleans the data according to the rules below, then returns one summary row.

Cleaning rules (apply in order):

1. Drop any row where id is NULL.
2. If pay is NULL, treat it as 0.
3. If churned is NULL and pay > 0, treat churned as 0 (the customer is considered active). Leave churned unchanged otherwise.
4. Define active_days as the number of days between signup_date and last_active_date (i.e. last_active_date - signup_date). If last_active_date < signup_date, set active_days to 0.

Metrics to compute over the cleaned rows:

• avg_active_days: the average of active_days across all remaining customers.
• avg_pay_churned: the average (cleaned) pay among customers whose cleaned churned = 1.

Output: return exactly one row with two columns, avg_active_days and avg_pay_churned, each rounded to 2 decimal places.

Table Customers

column	type	constraints
id	INT
signup_date	DATE
last_active_date	DATE
churned	SMALLINT
pay	DECIMAL(10,2)

Sample data:

churned	id	last_active_date	pay	signup_date
1	1	2025-03-05	99	2025-01-10
NULL	2	2025-02-20	15	2025-02-01
0	3	2025-01-18	NULL	2025-01-15
1	NULL	2025-02-12	50	2025-02-10
1	4	2025-02-05	25	2025-02-11

Expected output

One row: average active_days across all 4 valid (non-null id) customers, and average cleaned pay among the churned (churned=1) customers.

avg_active_days	avg_pay_churned
19.00	62.00

Hints

1. Do the cleaning in a CTE first, then aggregate the cleaned columns — keeps the final SELECT simple.
2. In PostgreSQL, date_a - date_b already returns the integer day difference; you do not need DATEDIFF.
3. For the churned-only average, put the filter inside the AVG with a CASE so non-churned rows become NULL and are skipped automatically.

Rank top students by average grade (top half by performance)

Using the schema below in MySQL 8.0, write ONE query that: (i) computes each student’s avg_score as the average of all their grades in the Grades table; (ii) excludes students who have zero completed assignments (i.e., no rows in Grades); (iii) among those students, keeps only the top CEIL(N/2) students by avg_score, where N is the number of students with at least one grade; break ties for ordering using student_id ASC; (iv) outputs columns student_id, name, avg_score (rounded to 1 decimal), and a dense_rank over the selected students ordered by avg_score DESC, student_id ASC; and (v) still works correctly as more students and grades are added.

Table Teachers

column	type	constraints
id	INT	PRIMARY KEY
name	VARCHAR(100)
classroom	VARCHAR(20)

Sample data:

classroom	id	name
301	4	Mr. Feeny
260	8	Mr. Cooper

Table Students

column	type	constraints
id	INT	PRIMARY KEY
name	VARCHAR(100)
primary_teacher_id	INT	FOREIGN KEY REFERENCES Teachers(id)

Sample data:

id	name	primary_teacher_id
1	Bobby	4
2	Susie	8
3	Deborah	8
9	Bruce	NULL

Table Assignments

column	type	constraints
id	INT	PRIMARY KEY
teacher_id	INT	FOREIGN KEY REFERENCES Teachers(id)

Sample data:

id	teacher_id
1	4
2	8

Table Grades

column	type	constraints
student_id	INT	FOREIGN KEY REFERENCES Students(id), PRIMARY KEY (student_id, assignment_id)
assignment_id	INT	FOREIGN KEY REFERENCES Assignments(id)
grade	DECIMAL(4,1)

Sample data:

assignment_id	grade	student_id
1	100	1
2	50	1
1	100	2
2	100	2
1	40	3
2	8	3
1	65	9
2	65	9

Expected output

Top half (CEIL(N/2)) of students by average grade, including their rounded average score and a dense_rank over the selected students ordered by avg_score DESC, student_id ASC.

avg_score	dense_rank	name	student_id
100	1	Susie	2
75	2	Bobby	1

Hints

1. First compute each student’s average grade using a GROUP BY over Grades joined to Students.
2. Use window functions both to determine the number of students (for CEIL(N/2)) and to assign row numbers and dense ranks for ordering and filtering.