select * from users where age * age > 2500;
select * from users where upper(displayname) = 'FRANZ';
select id from posts where closeddate is not null;
SELECT id FROM posts WHERE title LIKE 'pi%';
SELECT displayname AS name, location FROM users WHERE location LIKE 'Reims%';
select * from posthistory where extract('year' from creationdate) = 2010;
select * from comments where jsonfield is not null;
SELECT COUNT(ph.posthistorytypeid) AS nb, pht.name AS name FROM posthistory ph JOIN posthistorytypes pht ON (ph.posthistorytypeid = pht.id) WHERE ph.creationdate < '2011-01-01' GROUP BY 2;
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT u.displayname AS name , max(c.creationdate) AS date_last_comment FROM comments c JOIN users u ON (c.userid = u.id) WHERE userid = :user_id GROUP BY 1;
SELECT * FROM comments WHERE creationdate BETWEEN '2021-01-01' AND '2021-02-01';
SELECT * FROM interaction WHERE interac = 22;
SELECT DISTINCT location, age, displayname FROM users WHERE creationdate > '2021-01-01' ORDER BY 1,2 ;
SELECT * FROM users u JOIN votes v ON (u.id = v.userid) AND v.votetypeid = 5 JOIN votes v2 ON (u.id = v2.userid) AND v2.votetypeid = 6 JOIN votes v3 ON (u.id = v3.userid) AND v3.votetypeid = 8 JOIN votes v4 ON (u.id = v4.userid) AND v4.votetypeid = 10 JOIN votes v5 ON (u.id = v5.userid) AND v5.votetypeid = 4;

-- liste des badges d'un user
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT name FROM badges WHERE userid = :user_id;

-- type de votes par un user
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT COUNT(v.id) AS nb, vt.name AS name FROM votes v JOIN votetypes vt ON (v.votetypeid = vt.id) WHERE v.userid = :user_id GROUP BY 2;

-- liste des badges d'un user dont la date d'obtention est > 01/01/2020
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT name, date FROM badges WHERE userid = :user_id AND date > '2010-01-01';

-- récupérer tous les commentaires d'un post triés par date
SELECT id FROM posts WHERE random() < 0.01 LIMIT 1 \gset post_
SELECT text AS contenu, creationdate AS date FROM comments WHERE postid = :post_id ORDER BY 2 DESC;

select * from users where age * age > 2500;
select * from users where upper(displayname) = 'FRANZ';
select id from posts where closeddate is not null;
SELECT id FROM posts WHERE title LIKE 'pi%';
SELECT displayname AS name, location FROM users WHERE location LIKE 'Reims%';
select * from posthistory where extract('year' from creationdate) = 2010;
select * from comments where jsonfield is not null;
SELECT COUNT(ph.posthistorytypeid) AS nb, pht.name AS name FROM posthistory ph JOIN posthistorytypes pht ON (ph.posthistorytypeid = pht.id) WHERE ph.creationdate < '2011-01-01' GROUP BY 2;
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT u.displayname AS name , max(c.creationdate) AS date_last_comment FROM comments c JOIN users u ON (c.userid = u.id) WHERE userid = :user_id GROUP BY 1;
SELECT * FROM comments WHERE creationdate BETWEEN '2021-01-01' AND '2021-02-01';
SELECT * FROM interaction WHERE interac = 22;
SELECT DISTINCT location, age, displayname FROM users WHERE creationdate > '2021-01-01' ORDER BY 1,2 ;
SELECT * FROM users u JOIN votes v ON (u.id = v.userid) AND v.votetypeid = 5 JOIN votes v2 ON (u.id = v2.userid) AND v2.votetypeid = 6 JOIN votes v3 ON (u.id = v3.userid) AND v3.votetypeid = 8 JOIN votes v4 ON (u.id = v4.userid) AND v4.votetypeid = 10 JOIN votes v5 ON (u.id = v5.userid) AND v5.votetypeid = 4;

-- compter le nombre de commentaire pour un post
SELECT id FROM posts WHERE random() < 0.01 LIMIT 1 \gset post_
SELECT COUNT(id) AS nb_de_comments FROM comments WHERE postid = :post_id;

-- récupère l'historique d'un post
SELECT id FROM posts WHERE random() < 0.01 LIMIT 1 \gset post_
SELECT posttext as text, creationdate as date FROM posthistory WHERE postid = :post_id;

-- récupération des posts linkés et du type de link
SELECT pl.postid AS post, pl.relatedpostid AS post_link, plt.name as type_link FROM postlinks pl JOIN postlinktypes plt ON (pl.linktypeid = plt.id);

select * from users where age * age > 2500;
select * from users where upper(displayname) = 'FRANZ';
select id from posts where closeddate is not null;
SELECT id FROM posts WHERE title LIKE 'pi%';
SELECT displayname AS name, location FROM users WHERE location LIKE 'Reims%';
select * from posthistory where extract('year' from creationdate) = 2010;
select * from comments where jsonfield is not null;
SELECT COUNT(ph.posthistorytypeid) AS nb, pht.name AS name FROM posthistory ph JOIN posthistorytypes pht ON (ph.posthistorytypeid = pht.id) WHERE ph.creationdate < '2011-01-01' GROUP BY 2;
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT u.displayname AS name , max(c.creationdate) AS date_last_comment FROM comments c JOIN users u ON (c.userid = u.id) WHERE userid = :user_id GROUP BY 1;
SELECT * FROM comments WHERE creationdate BETWEEN '2021-01-01' AND '2021-02-01';
SELECT * FROM interaction WHERE interac = 22;
SELECT DISTINCT location, age, displayname FROM users WHERE creationdate > '2021-01-01' ORDER BY 1,2 ;
SELECT * FROM users u JOIN votes v ON (u.id = v.userid) AND v.votetypeid = 5 JOIN votes v2 ON (u.id = v2.userid) AND v2.votetypeid = 6 JOIN votes v3 ON (u.id = v3.userid) AND v3.votetypeid = 8 JOIN votes v4 ON (u.id = v4.userid) AND v4.votetypeid = 10 JOIN votes v5 ON (u.id = v5.userid) AND v5.votetypeid = 4;

-- statistique des posts pour un user
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT COUNT(p.posttypeid) AS nb, pt.name AS type FROM posts p JOIN posttypes pt ON (p.posttypeid = pt.id) WHERE owneruserid = :user_id group by 2;

-- recherche des posts fermés pour un user
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT id FROM posts WHERE closeddate IS NOT NULL AND owneruserid = :user_id;

-- récupération des tags pour un post
SELECT id FROM posts WHERE random() < 0.01 LIMIT 1 \gset post_
SELECT t.tagname AS tag FROM tags t JOIN posttags pt ON (t.id = pt.tagid) WHERE pt.postid = :post_id;

-- statistique age user
SELECT age, count(id) AS nb FROM users GROUP BY 1;

select * from users where age * age > 2500;
select * from users where upper(displayname) = 'FRANZ';
select id from posts where closeddate is not null;
SELECT id FROM posts WHERE title LIKE 'pi%';
SELECT displayname AS name, location FROM users WHERE location LIKE 'Reims%';
select * from posthistory where extract('year' from creationdate) = 2010;
select * from comments where jsonfield is not null;
SELECT COUNT(ph.posthistorytypeid) AS nb, pht.name AS name FROM posthistory ph JOIN posthistorytypes pht ON (ph.posthistorytypeid = pht.id) WHERE ph.creationdate < '2011-01-01' GROUP BY 2;
SELECT id FROM users WHERE random() < 0.01 LIMIT 1 \gset user_
SELECT u.displayname AS name , max(c.creationdate) AS date_last_comment FROM comments c JOIN users u ON (c.userid = u.id) WHERE userid = :user_id GROUP BY 1;
SELECT * FROM comments WHERE creationdate BETWEEN '2021-01-01' AND '2021-02-01';
SELECT * FROM interaction WHERE interac = 22;
SELECT DISTINCT location, age, displayname FROM users WHERE creationdate > '2021-01-01' ORDER BY 1,2 ;
SELECT * FROM users u JOIN votes v ON (u.id = v.userid) AND v.votetypeid = 5 JOIN votes v2 ON (u.id = v2.userid) AND v2.votetypeid = 6 JOIN votes v3 ON (u.id = v3.userid) AND v3.votetypeid = 8 JOIN votes v4 ON (u.id = v4.userid) AND v4.votetypeid = 10 JOIN votes v5 ON (u.id = v5.userid) AND v5.votetypeid = 4;