Skip to content

Instantly share code, notes, and snippets.

@realFranco
Last active February 18, 2023 19:31
Show Gist options
  • Save realFranco/277535cfc60f1c4451c04a85401a2872 to your computer and use it in GitHub Desktop.
Save realFranco/277535cfc60f1c4451c04a85401a2872 to your computer and use it in GitHub Desktop.
SQL - Test
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
#!/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
-- 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
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment