Day-1 and day-7 retention by signup cohort

Tables:

users(id int primary key, signed_up_at date)

activity(user_id int, activity_date date) (a user can have several rows for one day)

A user's cohort is the date they signed up. For each cohort, report the number of users and two retention ratios:

• day1_retention: the share of the cohort active exactly 1 day after signup.
• day7_retention: the share active exactly 7 days after signup.

Round ratios to 2 decimals. Include cohorts with no activity (ratios 0).

Output columns: cohort, users, day1_retention, day7_retention.

CREATE TABLE users (
  id int PRIMARY KEY,
  signed_up_at date NOT NULL
);
CREATE TABLE activity (
  user_id int NOT NULL,
  activity_date date NOT NULL
);