SQL - Test

compose.yml

services:
    db:
        image: postgres:latest
        container_name: pgsql
        hostname: myhost
        restart: always
        environment:
            POSTGRES_DB: test_db
            POSTGRES_USER: root
            POSTGRES_PASSWORD: root
        volumes:
            - ./pg_data:/var/lib/postgresql/data/
        ports:
            - "5432:5432"
        networks:
            - network

networks:
    network:
        driver: bridge

run.sh

#!/bin/bash

# Run the container.
docker compose -f ./compose.yaml up

# Use another bash tab or initialize the container in detach mode.
docker exec -it pgsql bash

# Inside the container, runs:
psql -p 5432 -U root -d test_db

sql_andalucia.sql

-- Date: Feb. 18, 2023.
-- SQL from Andalucia.
-- This challenge was not solved by me the day when was asked the question (Feb. 17).
-- After think a little bit more I finally expose this query in order to
-- answer the query.

-- create schema myschema;

create table capacity_elements (
  id int, 
  event_id int, 
  version_id int, 
  capacity int, 
  primary key (id)
);
insert into capacity_elements(
  id, event_id, version_id, capacity
) 
values 
  (1, 1, 1, 50), 
  (2, 1, 2, 23), 
  (3, 1, 3, 21), 
  (4, 2, 1, 50), 
  (5, 2, 2, 50), 
  (6, 2, 3, 50), 
  (1, 1, 1, 50);

/*
select * from capacity_elements;

select 
  event_id, version_id, capacity
from
  capacity_elements
where
  version_id = 3;

select
  distinct(event_id), max(version_id) as "version_id"
from
  capacity_elements
group by 
  event_id
;
*/

select 
  ce.id, 
  ce.event_id, 
  ce.version_id, 
  ce.capacity 
from 
  (
    select 
      distinct(event_id), 
      max(version_id) as "version_id" 
    from 
      capacity_elements 
    group by 
      event_id
  ) as sub_ce 
  join capacity_elements as ce ON 
    sub_ce.event_id = ce.event_id 
    and sub_ce.version_id = ce.version_id;

/*
 id | event_id | version_id | capacity 
----+----------+------------+----------
  3 |        1 |          3 |       21
  6 |        2 |          3 |       50
  7 |        3 |          1 |       50
*/