Schema + sample data for regs / subs over time SQL question

bootstrap.sql

# Drop the Coderpad default tables
drop table if exists alerts;
drop table if exists departments;
drop table if exists employees;
drop table if exists employees_projects;
drop table if exists projects;
drop table if exists sales;
drop table if exists signup_flow_events;
drop table if exists stores;
drop table if exists products;
drop table if exists customers;
drop table if exists users;
drop table if exists subscriptions;

create table users
(
  id int(11) not null auto_increment,
  created_at datetime,
  primary key(id)
);

create table subscriptions
(
  user_id int(11) not null,
  conversion_time date not null,
  active_until date not null,
  primary key(user_id, conversion_time)
);

insert into users (created_at) values('2014-1-01');
insert into users (created_at) values('2014-1-01');
insert into users (created_at) values('2014-2-01');
insert into users (created_at) values('2014-2-01');
insert into users (created_at) values('2014-2-01');
insert into users (created_at) values('2014-2-01');
insert into users (created_at) values('2014-3-01');
insert into users (created_at) values('2014-3-01');
insert into users (created_at) values('2014-3-01');
insert into users (created_at) values('2014-3-01');
insert into users (created_at) values('2014-3-01');
insert into users (created_at) values('2014-3-01');
insert into users (created_at) values('2014-4-01');
insert into users (created_at) values('2014-4-01');
insert into users (created_at) values('2014-4-01');
insert into users (created_at) values('2014-4-01');
insert into users (created_at) values('2014-4-01');
insert into users (created_at) values('2014-4-01');
insert into users (created_at) values('2014-4-01');
insert into users (created_at) values('2014-4-01');

insert into subscriptions (user_id, conversion_time, active_until) values (3,   '2014-02-01', '2014-03-31');
insert into subscriptions (user_id, conversion_time, active_until) values (4,   '2014-02-01', '2014-03-31');
insert into subscriptions (user_id, conversion_time, active_until) values (7,   '2014-03-01', '2014-03-31');
insert into subscriptions (user_id, conversion_time, active_until) values (8,   '2014-03-01', '2014-03-31');
insert into subscriptions (user_id, conversion_time, active_until) values (9,   '2014-03-01', '2014-03-31');
insert into subscriptions (user_id, conversion_time, active_until) values (10,  '2014-03-01', '2014-03-31');
insert into subscriptions (user_id, conversion_time, active_until) values (14,  '2014-04-01', '2014-12-31');
insert into subscriptions (user_id, conversion_time, active_until) values (15,  '2014-04-01', '2014-12-31');
insert into subscriptions (user_id, conversion_time, active_until) values (16,  '2014-04-01', '2014-12-31');

intro.sql

/*
  users.id (integer)
  users.created_at (timestamp)
  
  subscriptions.user_id (integer)
  subscriptions.conversion_time (timestamp)
  subscriptions.active_until (timestamp)

1. Cumulative registrations over time
  month     # regs
  ----------------
  2014-1    100
  2014-2    200
  2014-3    400

2. Current subscriptions by month
  month     # subs
  ----------------
  2014-1    100
  2014-2    200
  2014-3    400
  2014-4    200

MySQL date format for months: `date_format(timestamp, '%Y-%m')`
*/