---
# System prepended metadata

title: Untitled

---

B(osoba, bar), czyli bywa osoba w barze,
P(sok, bar), czyli podają sok w barze,
L(osoba, sok), czyli lubi osoba sok.
    
Bary, do których chodzą tylko osoby, które lubią wszystkie podawane w nich soki

$$
\{ b\ |\ (\forall p\ \forall s)(B(p,b) \land P(s,b) \implies L(p,s) ) \}
$$

```sql
CREATE VIEW users_with_badge AS
SELECT DISTINCT b.userid as id FROM badges b WHERE b.name='Enlightened';

CREATE VIEW average(n) AS
SELECT avg(upvotes) FROM users u JOIN users_with_badge ub ON u.id = ub.id;
-- 171.2439024390243902

CREATE VIEW users_with_more_than_one_comment AS
SELECT c.userid as id FROM comments c JOIN posts p ON p.id = c.postid
    WHERE EXTRACT(YEAR FROM p.creationdate)=2020
    GROUP BY c.userid
    HAVING count(c.userid)>1;

CREATE VIEW users_without_badge AS
(SELECT id FROM users)
EXCEPT
(SELECT id FROM users_with_badge );

SELECT u.id as id, displayname, reputation  FROM users u
    JOIN users_without_badge uwb ON u.id = uwb.id
    JOIN users_with_more_than_one_comment uwmtoc ON u.id = uwmtoc.id
    JOIN average a
    WHERE u.upvotes > a.n
    ORDER BY u.creationdate ASC;
```