Counting booleans in GROUP BY sections

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Counting booleans in GROUP BY sections

Alexander Farber
Good evening,

I am trying to count the booleans per each GROUP BY section by the following stored function:

CREATE OR REPLACE FUNCTION words_list_puzzles(
                in_start interval,
                in_end interval

        ) RETURNS TABLE (
                out_label  text,
                out_count  bigint,
                out_puzzle boolean,
                out_mid    bigint,
                out_secret text,
                out_gid    integer,
                out_score  integer
        ) AS
$func$

    SELECT
        TO_CHAR(played, 'Mon YYYY') AS label,
        COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?
        puzzle,
        mid,
        MD5(mid || ‘my little secret’) AS secret,
        gid,
        score

    FROM words_moves
    WHERE action = 'play'
    AND LENGTH(hand) = 7
    AND (LENGTH(letters) = 7 OR score > 90)
    AND played > CURRENT_TIMESTAMP - in_start
    AND played < CURRENT_TIMESTAMP - in_end
    GROUP BY label, puzzle, mid, secret, gid, score
    ORDER BY played DESC

$func$ LANGUAGE sql STABLE;

But when I run it, I only get 0 or 1 in the out_count column:

words_ru=> select * from words_list_puzzles(interval '2 year', interval '1 year');
 out_label | out_count | out_puzzle | out_mid |            out_secret            | out_gid | out_score
-----------+-----------+------------+---------+----------------------------------+---------+-----------
 Nov 2018  |         0 | f          | 1326876 | e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
 Nov 2018  |         0 | f          | 1324466 | 6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
 Nov 2018  |         0 | f          | 1322050 | b67b091d383678de392bf7370c735cab |   45877 |        34
 Nov 2018  |         0 | f          | 1320017 | 35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
 Nov 2018  |         0 | f          | 1319160 | 83df42f7ad398bbb060fc02ddfdc62c0 |   45031 |        95
.....
 May 2018  |         0 | f          |  264251 | 2fff1154962966b16a2996387e30ae7f |   10946 |        99
 May 2018  |         1 | t          |  257620 | 645613db6ea40695dc967d8090ab3246 |   12713 |        93
 May 2018  |         0 | f          |  245792 | bb75bfd9cb443ff541b199d893c68117 |   12359 |        24
 May 2018  |         1 | t          |  243265 | d899a5d642ccd96d931194f48ef56d53 |   11950 |       123
 May 2018  |         0 | f          |  231953 | ad53b5b2c0d4cced3d50e8b44ad53e55 |   11910 |        32

- while I was hoping to get 2 for the "May 2018" section.

What am I doing wrong please, why don't the values add up? Below is the table desc:

words_ru=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |                 Default                  
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null | nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 letters | text                     |           |          |
 hand    | text                     |           |          |
 puzzle  | boolean                  |           | not null | false
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_action_played_idx" btree (uid, action, played)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE

Thank you
Alex

Reply | Threaded
Open this post in threaded view
|

Re: Counting booleans in GROUP BY sections

Adrian Klaver-4
On 11/29/19 8:38 AM, Alexander Farber wrote:

> Good evening,
>
> I am trying to count the booleans per each GROUP BY section by the
> following stored function:
>
> CREATE OR REPLACE FUNCTION words_list_puzzles(
>                  in_start interval,
>                  in_end interval
>
>          ) RETURNS TABLE (
>                  out_label  text,
>                  out_count  bigint,
>                  out_puzzle boolean,
>                  out_mid    bigint,
>                  out_secret text,
>                  out_gid    integer,
>                  out_score  integer
>          ) AS
> $func$
>
>      SELECT
>          TO_CHAR(played, 'Mon YYYY') AS label,
>          COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?

If I am following it is because you have mid in GROUP BY and mid is a
PK. Since mid will always be unique you will have at most on row per group.

>          puzzle,
>          mid,
>          MD5(mid || ‘my little secret’) AS secret,
>          gid,
>          score
>
>      FROM words_moves
>      WHERE action = 'play'
>      AND LENGTH(hand) = 7
>      AND (LENGTH(letters) = 7 OR score > 90)
>      AND played > CURRENT_TIMESTAMP - in_start
>      AND played < CURRENT_TIMESTAMP - in_end
>      GROUP BY label, puzzle, mid, secret, gid, score
>      ORDER BY played DESC
>
> $func$ LANGUAGE sql STABLE;
>
> But when I run it, I only get 0 or 1 in the out_count column:
>
> words_ru=> select * from words_list_puzzles(interval '2 year', interval
> '1 year');
>   out_label | out_count | out_puzzle | out_mid |            out_secret  
>           | out_gid | out_score
> -----------+-----------+------------+---------+----------------------------------+---------+-----------
>   Nov 2018  |         0 | f          | 1326876 |
> e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
>   Nov 2018  |         0 | f          | 1324466 |
> 6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
>   Nov 2018  |         0 | f          | 1322050 |
> b67b091d383678de392bf7370c735cab |   45877 |        34
>   Nov 2018  |         0 | f          | 1320017 |
> 35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
>   Nov 2018  |         0 | f          | 1319160 |
> 83df42f7ad398bbb060fc02ddfdc62c0 |   45031 |        95
> .....
>   May 2018  |         0 | f          |  264251 |
> 2fff1154962966b16a2996387e30ae7f |   10946 |        99
>   May 2018  |         1 | t          |  257620 |
> 645613db6ea40695dc967d8090ab3246 |   12713 |        93
>   May 2018  |         0 | f          |  245792 |
> bb75bfd9cb443ff541b199d893c68117 |   12359 |        24
>   May 2018  |         1 | t          |  243265 |
> d899a5d642ccd96d931194f48ef56d53 |   11950 |       123
>   May 2018  |         0 | f          |  231953 |
> ad53b5b2c0d4cced3d50e8b44ad53e55 |   11910 |        32
>
> - while I was hoping to get 2 for the "May 2018" section.
>
> What am I doing wrong please, why don't the values add up? Below is the
> table desc:
>
> words_ru=> \d words_moves
>                                        Table "public.words_moves"
>   Column  |           Type           | Collation | Nullable |          
>        Default
> ---------+--------------------------+-----------+----------+------------------------------------------
>   mid     | bigint                   |           | not null |
> nextval('words_moves_mid_seq'::regclass)
>   action  | text                     |           | not null |
>   gid     | integer                  |           | not null |
>   uid     | integer                  |           | not null |
>   played  | timestamp with time zone |           | not null |
>   tiles   | jsonb                    |           |          |
>   score   | integer                  |           |          |
>   letters | text                     |           |          |
>   hand    | text                     |           |          |
>   puzzle  | boolean                  |           | not null | false
> Indexes:
>      "words_moves_pkey" PRIMARY KEY, btree (mid)
>      "words_moves_gid_played_idx" btree (gid, played DESC)
>      "words_moves_uid_action_played_idx" btree (uid, action, played)
>      "words_moves_uid_idx" btree (uid)
> Check constraints:
>      "words_moves_score_check" CHECK (score >= 0)
> Foreign-key constraints:
>      "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES
> words_games(gid) ON DELETE CASCADE
>      "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES
> words_users(uid) ON DELETE CASCADE
> Referenced by:
>      TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>
> Thank you
> Alex
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Counting booleans in GROUP BY sections

Alexander Farber
Thank you Adrian, but -

On Fri, Nov 29, 2019 at 6:45 PM Adrian Klaver <[hidden email]> wrote:
On 11/29/19 8:38 AM, Alexander Farber wrote:
>
> CREATE OR REPLACE FUNCTION words_list_puzzles(
>                  in_start interval,
>                  in_end interval
>
>          ) RETURNS TABLE (
>                  out_label  text,
>                  out_count  bigint,
>                  out_puzzle boolean,
>                  out_mid    bigint,
>                  out_secret text,
>                  out_gid    integer,
>                  out_score  integer
>          ) AS
> $func$
>
>      SELECT
>          TO_CHAR(played, 'Mon YYYY') AS label,
>          COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?

If I am following it is because you have mid in GROUP BY and mid is a
PK. Since mid will always be unique you will have at most on row per group.

>          puzzle,
>          mid,
>          MD5(mid || ‘my little secret’) AS secret,
>          gid,
>          score
>
>      FROM words_moves
>      WHERE action = 'play'
>      AND LENGTH(hand) = 7
>      AND (LENGTH(letters) = 7 OR score > 90)
>      AND played > CURRENT_TIMESTAMP - in_start
>      AND played < CURRENT_TIMESTAMP - in_end
>      GROUP BY label, puzzle, mid, secret, gid, score
>      ORDER BY played DESC
>
> $func$ LANGUAGE sql STABLE;
>
> But when I run it, I only get 0 or 1 in the out_count column:
>
> words_ru=> select * from words_list_puzzles(interval '2 year', interval
> '1 year');
>   out_label | out_count | out_puzzle | out_mid |            out_secret   
>           | out_gid | out_score
> -----------+-----------+------------+---------+----------------------------------+---------+-----------
>   Nov 2018  |         0 | f          | 1326876 |
> e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
>   Nov 2018  |         0 | f          | 1324466 |
> 6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
>   Nov 2018  |         0 | f          | 1322050 |
> b67b091d383678de392bf7370c735cab |   45877 |        34
>   Nov 2018  |         0 | f          | 1320017 |
> 35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
>   Nov 2018  |         0 | f          | 1319160 |
> 83df42f7ad398bbb060fc02ddfdc62c0 |   45031 |        95
> .....
>   May 2018  |         0 | f          |  264251 |
> 2fff1154962966b16a2996387e30ae7f |   10946 |        99
>   May 2018  |         1 | t          |  257620 |
> 645613db6ea40695dc967d8090ab3246 |   12713 |        93
>   May 2018  |         0 | f          |  245792 |
> bb75bfd9cb443ff541b199d893c68117 |   12359 |        24
>   May 2018  |         1 | t          |  243265 |
> d899a5d642ccd96d931194f48ef56d53 |   11950 |       123
>   May 2018  |         0 | f          |  231953 |
> ad53b5b2c0d4cced3d50e8b44ad53e55 |   11910 |        32
>
> - while I was hoping to get 2 for the "May 2018" section.
>
> What am I doing wrong please, why don't the values add up? Below is the
> table desc:
>
> words_ru=> \d words_moves
>                                        Table "public.words_moves"
>   Column  |           Type           | Collation | Nullable |           
>        Default
> ---------+--------------------------+-----------+----------+------------------------------------------
>   mid     | bigint                   |           | not null |
> nextval('words_moves_mid_seq'::regclass)
>   action  | text                     |           | not null |
>   gid     | integer                  |           | not null |
>   uid     | integer                  |           | not null |
>   played  | timestamp with time zone |           | not null |
>   tiles   | jsonb                    |           |          |
>   score   | integer                  |           |          |
>   letters | text                     |           |          |
>   hand    | text                     |           |          |
>   puzzle  | boolean                  |           | not null | false
> Indexes:
>      "words_moves_pkey" PRIMARY KEY, btree (mid)
>      "words_moves_gid_played_idx" btree (gid, played DESC)
>      "words_moves_uid_action_played_idx" btree (uid, action, played)
>      "words_moves_uid_idx" btree (uid)
> Check constraints:
>      "words_moves_score_check" CHECK (score >= 0)
> Foreign-key constraints:
>      "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES
> words_games(gid) ON DELETE CASCADE
>      "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES
> words_users(uid) ON DELETE CASCADE
> Referenced by:
>      TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>


if I remove GROUP BY mid, then I get the error:

ERROR:  42803: column "words_moves.mid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 18:         mid,
                 ^
LOCATION:  check_ungrouped_columns_walker, parse_agg.c:1369

Regards
Alex
Reply | Threaded
Open this post in threaded view
|

Re: Counting booleans in GROUP BY sections

David G Johnston
On Fri, Nov 29, 2019 at 12:48 PM Alexander Farber <[hidden email]> wrote:

if I remove GROUP BY mid, then I get the error:

ERROR:  42803: column "words_moves.mid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 18:         mid,
                 ^
LOCATION:  check_ungrouped_columns_walker, parse_agg.c:1369


Yes, you need to decide whether you want to output GROUPS (in which case any detail more specific than your desired group needs to be aggregated) or NOT (in which case you can probably use WINDOW functions to accomplish your goal - count(...) OVER (PARTITION BY <the level of grouping you desire knowledge about - year month it seems in this case>))

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Counting booleans in GROUP BY sections

Adrian Klaver-4
In reply to this post by Alexander Farber
On 11/29/19 11:47 AM, Alexander Farber wrote:
> Thank you Adrian, but -

>
> if I remove GROUP BY mid, then I get the error:
>
> ERROR:  42803: column "words_moves.mid" must appear in the GROUP BY
> clause or be used in an aggregate function
> LINE 18:         mid,
>                   ^
> LOCATION:  check_ungrouped_columns_walker, parse_agg.c:1369

Yes because it is a case of opposing forces. When you remove mid from
the GROUP BY you get an single row for each group that has an aggregated
output where you can have count of > 1. In that case the database has
more then one choice for the mid to display and so it throws the error.

To go forward it would help to know what it is you are trying to achieve?

>
> Regards
> Alex


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Counting booleans in GROUP BY sections

Alexander Farber
My context is that I have a table of player moves with PK mid (aka "move id").

And I am able to find "interesting" moves by the high score or all 7 letter tiles used.

But I do some human reviewing and set a "puzzle" boolean for truly interesting moves.

For the reviewing tool I would like to display headers: a "Mon YYYY" plus the number of true puzzles per section.

Thanks to David's hint the following seems to work even though I wonder if it is the most optimal way to call TO_CHAR twice:

CREATE OR REPLACE FUNCTION words_list_puzzles(
                in_start interval,
                in_end interval
        ) RETURNS TABLE (
                out_label  text,
                out_count  bigint,
                out_puzzle boolean,
                out_mid    bigint,
                out_secret text,
                out_gid    integer,
                out_score  integer
        ) AS
$func$
    SELECT
        TO_CHAR(played, 'Mon YYYY') AS label,                                                                                -- used for header
        COUNT(NULLIF(puzzle, FALSE)) OVER (PARTITION BY TO_CHAR(played, 'Mon YYYY')), --used for header
        puzzle,
        mid,
        MD5(mid || 'my little secret') AS secret,
        gid,
        score
    FROM words_moves
    WHERE action = 'play'
    AND LENGTH(hand) = 7
    AND (LENGTH(letters) = 7 OR score > 90)
    AND played > CURRENT_TIMESTAMP - in_start
    AND played < CURRENT_TIMESTAMP - in_end
    ORDER BY played DESC
$func$ LANGUAGE sql STABLE;

Regards
Alex

P.S: Below is my table description again and the output of the above function:

words_ru=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |                 Default                  
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null | nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 letters | text                     |           |          |
 hand    | text                     |           |          |
 puzzle  | boolean                  |           | not null | false
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_action_played_idx" btree (uid, action, played)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE

words_ru=> select * from words_list_puzzles(interval '2 year', interval '1 year');
 out_label | out_count | out_puzzle | out_mid |            out_secret            | out_gid | out_score
-----------+-----------+------------+---------+----------------------------------+---------+-----------
 Nov 2018  |         1 | f          | 1331343 | 78467b5f3bde3d3f2291cf539c949f79 |   46134 |        28
 Nov 2018  |         1 | f          | 1326876 | e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
 Nov 2018  |         1 | f          | 1324466 | 6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
 Nov 2018  |         1 | f          | 1322050 | b67b091d383678de392bf7370c735cab |   45877 |        34
 Nov 2018  |         1 | f          | 1320017 | 35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
.....
 May 2018  |         3 | f          |   95114 | e7e8bab64fab20f6fec229319e2bab40 |    7056 |        28
 May 2018  |         3 | f          |   88304 | 161c0638dede80f830a36efa6f428dee |    6767 |        40
 May 2018  |         3 | f          |   86180 | 4d47a65263331cf4e2d2956886b6a72f |    6706 |        26
 May 2018  |         3 | f          |   85736 | debb1efd673c91947a8aa7f38be4217c |    6680 |        28
 May 2018  |         3 | f          |   82522 | e55ec68a5a5dacc2bc463e397198cb1c |    6550 |        27
 Apr 2018  |         0 | f          |   78406 | f5d264ccfe94aaccd90ce6c019716d4d |    5702 |        58
 Apr 2018  |         0 | f          |   77461 | 404886e913b698596f9cf3648ddf6fa4 |    1048 |        26
(415 rows)
Reply | Threaded
Open this post in threaded view
|

Re: Counting booleans in GROUP BY sections

Adrian Klaver-4
On 11/30/19 4:08 AM, Alexander Farber wrote:

> My context is that I have a table of player moves with PK mid (aka "move
> id").
>
> And I am able to find "interesting" moves by the high score or all 7
> letter tiles used.
>
> But I do some human reviewing and set a "puzzle" boolean for truly
> interesting moves.
>
> For the reviewing tool I would like to display headers: a "Mon YYYY"
> plus the number of true puzzles per section.
>
> Thanks to David's hint the following seems to work even though I wonder
> if it is the most optimal way to call TO_CHAR twice:

Given that played contains values, I assume, that are at multiple points
in a month and you want the 'group' to be a month it looks alright to
me. Though if it bothers you then another option is date_trunc():

test=# select date_trunc('month', '11/02/2019 13:00'::timestamp),
date_trunc('month', '11/23/2019 13:00'::timestamp);
      date_trunc      |     date_trunc
---------------------+---------------------
  11/01/2019 00:00:00 | 11/01/2019 00:00:00


>
> CREATE OR REPLACE FUNCTION words_list_puzzles(
>                  in_start interval,
>                  in_end interval
>          ) RETURNS TABLE (
>                  out_label  text,
>                  out_count  bigint,
>                  out_puzzle boolean,
>                  out_mid    bigint,
>                  out_secret text,
>                  out_gid    integer,
>                  out_score  integer
>          ) AS
> $func$
>      SELECT
>          TO_CHAR(played, 'Mon YYYY') AS label,                          
>                                                        -- used for header
>          COUNT(NULLIF(puzzle, FALSE)) OVER (PARTITION BY TO_CHAR(played,
> 'Mon YYYY')), --used for header
>          puzzle,
>          mid,
>          MD5(mid || 'my little secret') AS secret,
>          gid,
>          score
>      FROM words_moves
>      WHERE action = 'play'
>      AND LENGTH(hand) = 7
>      AND (LENGTH(letters) = 7 OR score > 90)
>      AND played > CURRENT_TIMESTAMP - in_start
>      AND played < CURRENT_TIMESTAMP - in_end
>      ORDER BY played DESC
> $func$ LANGUAGE sql STABLE;
>
> Regards
> Alex
>
> P.S: Below is my table description again and the output of the above
> function:
>
> words_ru=> \d words_moves
>                                        Table "public.words_moves"
>   Column  |           Type           | Collation | Nullable |          
>        Default
> ---------+--------------------------+-----------+----------+------------------------------------------
>   mid     | bigint                   |           | not null |
> nextval('words_moves_mid_seq'::regclass)
>   action  | text                     |           | not null |
>   gid     | integer                  |           | not null |
>   uid     | integer                  |           | not null |
>   played  | timestamp with time zone |           | not null |
>   tiles   | jsonb                    |           |          |
>   score   | integer                  |           |          |
>   letters | text                     |           |          |
>   hand    | text                     |           |          |
>   puzzle  | boolean                  |           | not null | false
> Indexes:
>      "words_moves_pkey" PRIMARY KEY, btree (mid)
>      "words_moves_gid_played_idx" btree (gid, played DESC)
>      "words_moves_uid_action_played_idx" btree (uid, action, played)
>      "words_moves_uid_idx" btree (uid)
> Check constraints:
>      "words_moves_score_check" CHECK (score >= 0)
> Foreign-key constraints:
>      "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES
> words_games(gid) ON DELETE CASCADE
>      "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES
> words_users(uid) ON DELETE CASCADE
> Referenced by:
>      TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>
> words_ru=> select * from words_list_puzzles(interval '2 year', interval
> '1 year');
>   out_label | out_count | out_puzzle | out_mid |            out_secret  
>           | out_gid | out_score
> -----------+-----------+------------+---------+----------------------------------+---------+-----------
>   Nov 2018  |         1 | f          | 1331343 |
> 78467b5f3bde3d3f2291cf539c949f79 |   46134 |        28
>   Nov 2018  |         1 | f          | 1326876 |
> e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
>   Nov 2018  |         1 | f          | 1324466 |
> 6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
>   Nov 2018  |         1 | f          | 1322050 |
> b67b091d383678de392bf7370c735cab |   45877 |        34
>   Nov 2018  |         1 | f          | 1320017 |
> 35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
> .....
>   May 2018  |         3 | f          |   95114 |
> e7e8bab64fab20f6fec229319e2bab40 |    7056 |        28
>   May 2018  |         3 | f          |   88304 |
> 161c0638dede80f830a36efa6f428dee |    6767 |        40
>   May 2018  |         3 | f          |   86180 |
> 4d47a65263331cf4e2d2956886b6a72f |    6706 |        26
>   May 2018  |         3 | f          |   85736 |
> debb1efd673c91947a8aa7f38be4217c |    6680 |        28
>   May 2018  |         3 | f          |   82522 |
> e55ec68a5a5dacc2bc463e397198cb1c |    6550 |        27
>   Apr 2018  |         0 | f          |   78406 |
> f5d264ccfe94aaccd90ce6c019716d4d |    5702 |        58
>   Apr 2018  |         0 | f          |   77461 |
> 404886e913b698596f9cf3648ddf6fa4 |    1048 |        26
> (415 rows)


--
Adrian Klaver
[hidden email]