Ruby and SQL exercises

ranges.rb

def ranges(sequence)
  return sequence.to_s if sequence.is_a? Numeric

  sequence.inject([]) { |result, n|
    if result.empty?
      result << n
    elsif result.last.is_a?(Numeric) && (result.last - n).abs == 1
      result[-1] = [result.last, n]
      result
    elsif result.last[1] - result.last[0] == n - result.last[1]
      result[-1] = [result.last[0], n]
      result
    else
      result << n
    end
  }.map { |n| n.is_a?(Array) ? n.join(':') : n }.join(',')
end

ranges_spec.rb

require './ranges'

# Convert a list of not-necessarily sequential
# numbers into a comma-separated list of
# ranges, of the format "from:to".
# Example: ranges(1..2) = "1:2"
# Example: ranges([1,2,3,5]) = "1:3,5"
# Example: ranges(1) = "1"

describe 'ranges' do
  it 'accepts a range as parameter' do
    ranges(1..2).should eq("1:2")
  end

  it 'accepts an array as parameter' do
    ranges([1,2,3]).should eq("1:3")
  end

  it 'accepts an integer as parameter' do
    ranges(1).should eq("1")
  end

  it 'converts sequencial numbers into "from:to" format' do
    ranges(1..2).should eq("1:2")
  end

  it 'returns comma-separated list of non-sequential numbers' do
    ranges([1,3]).should eq("1,3")
  end

  it 'accepts decrescent sequences' do
    ranges([3,2,1]).should eq("3:1")
  end
end

sequence.rb

def sequence(ranges)
  ranges.split(',').map { |n|
    range = n.split(':').map { |s| Integer(s) }
    (range[0]..range[-1]).to_a
  }.flatten
end

sequence_spec.rb

require './sequence'

# Example: sequence("1")=[1]
# Example: sequence("1,2")=[1,2]
# Example: sequence("1:3,5")=[1,2,3,5]

describe 'sequence' do
  it 'returns an unitary array when range only has numbers' do
    sequence("1").should eq([1])
  end

  it 'returns an array with two elements when range has a comma' do
    sequence("1,2").should eq([1,2])
  end

  it 'generates a sequence of numbers when range has a colon' do
    sequence("1:3").should eq([1,2,3])
  end

  it 'handles sequences with comma and colon' do
    sequence("1:3,5").should eq([1,2,3,5])
  end
end

staffmember_meeting.sql

-- 3) You are given a database for a basic Staffmember-Meeting management
-- app. The application, which uses this database, shall have the purpose
-- of logging which Staffmembers attended certain Meetings. Staffmembers
-- can either be managers or ordinary staff. The Staffmember-Meeting
-- relationship shall be n:n

-- a) Write the SQL create statements for all tables in the database

CREATE TABLE meetings (
  id SERIAL PRIMARY KEY
);

CREATE TYPE member_type AS ENUM ('manager', 'ordinary');
CREATE TABLE members (
  id SERIAL PRIMARY KEY,
  type member_type
);

CREATE TABLE meetings_members (
  meeting_id INT NOT NULL,
  member_id INT NOT NULL,
  PRIMARY KEY(meeting_id, member_id),
  FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
);

-- b) Create an SQL query, based on your create statements, that returns
-- the number of "Manager meetings", i.e. the number of meetings which
-- have been attended by Managers only.

SELECT COUNT(1) AS manager_meetings FROM meetings WHERE id NOT IN (SELECT meeting_id FROM meetings_members JOIN members ON member_id = id WHERE members.type != 'manager' GROUP BY meeting_id);