SQL katas

lxft-logins.sql

-- Given two tables users and login records show first login time on each day
-- SQL is for Postgres

create table users (
  name varchar(64) primary key
);
create table log (
  user_name varchar(64) references users (name) not null,
  date      timestamp
);

insert into users values
  ('a'),
  ('b');

insert into log values
  ('a', '2014-01-05 10:34'),
  ('a', '2014-01-05 12:34'),
  ('a', '2014-01-06 09:22'),
  ('b', '2014-01-05 12:34'),
  ('b', '2014-01-07 10:00'),
  ('b', '2014-01-07 18:34');

select
  users.name,
  min(log.date)
from users
  left join log on users.name = log.user_name
group by users.name, log.date
order by log.date;

-- Cleanup
drop table log;
drop table users;

lxft-month-counts.sql

-- Given set of <date, count> pairs. have sums of counts per month per given year, output month sums from beginning of year to current month inclusively
-- SQL is for Postgres

create table date_counts (
  date  timestamp,
  count bigint
);

insert into date_counts values
  ('2014-01-15', 1),
  ('2014-01-17', 3),
  ('2014-03-02', 10),
  ('2014-04-30', 200);

-- Using window function
select 
  month,
  sum(month_count) over (order by month) as counts 
from (select
        date_part('month', date) as month,
        sum(count) month_count
      from date_counts
      where date_part('year', date) = 2014
      group by month) month_counts;

-- With sub-selects
with month_counts as (select
                        sum(count) counts,
                        date_part('month', date) as month
                      from date_counts
                      where date_part('year', date) = 2014
                      group by month)
select
  months.month,
  sum(month_sums.counts) as counts
from
  month_counts as months,
  month_counts as month_sums
where month_sums.month <= months.month
group by months.month;

-- The query should yield
-- month, counts
-- 1,     4
-- 3,     14
-- 4,     214

drop table date_counts; -- Cleanup