I have a table that I would like as a leader-board for invitations as described below. I would like to create a query that counts the number of duplicate rows in a given month and order in a descending fashion.

Reading through some questions, this query seems to work:

SELECT COUNT(invite_code) AS counted
FROM invite_table
GROUP BY invite_code

But it doesn't consider the month. What I am looking for is to get the most frequently appearing user_code where the month is specified. Also any criticism about the table design is welcome as I have deliberately designed it such that there are repeating rows with duplicate values. I am trying to track users whose invite code is used the most in a given month, I also have codes that indicate which channel a user comes from (maybe seeing an ad in FB for example), is this a valid table design?

Table "public.invite_table"
   Column    |         Type         | Collation | Nullable | Default
 user_code   | character varying    |           | not null |
 invite_code | character varying    |           |          |
 month       | character varying(3) |           | not null |
 points      | integer              |           | not null |
    "invite_table_pkey" PRIMARY KEY, btree (user_code)
Foreign-key constraints:
    "invite_table_user_code_fkey" FOREIGN KEY (user_code) REFERENCES user_table(user_code)
  • Could you provide sample data and your desired result? Jul 6, 2018 at 19:02
  • I don't understand what you mean when you say "Find most frequent values for a given column" it seems as if you're counting in your example, and in the chosen answer. Do you need the one with the highest count, or the top 10 (you have LIMIT 10)? I'm not sure what's going on here. Jul 6, 2018 at 19:06

1 Answer 1


get the most frequently appearing user_code where the month is specified

Since user_code is the primary key, that question would be nonsense. There can never be more than one. I assume you meant invite_code?

Just add a WHERE clause. And since the column can be null, also consider excluding null values:

SELECT invite_code, COUNT(*) AS counted
FROM   invite_table
WHERE  month = 'May'  -- or whatever is stored in your varchar(3) column
AND    invite_code IS NOT NULL  -- exclude NULL
GROUP  BY invite_code
ORDER  BY counted DESC, invite_code  -- break ties in deterministic fashion
LIMIT  10;

Month, date, timestamp?

A month column as varchar(3) doesn't seem very useful if there can be data for more than a single year. I would use data type date for it. You can format that with to_char() any way you like for presentation. Like:

SELECT to_char(date '2017-12-01', 'Mon');  -- 'Dec'

The column could look like this (also addressing your comment):

, inserted_at date DEFAULT CURRENT_DATE

The default value is entered when the column is omitted in an INSERT statement.

Or, if really only the month is relevant:

... DEFAULT date_trunc('month', LOCALTIMESTAMP)::date

Either introduces a dependency on the timezone setting of the current session. I would rather store the complete timestamptz to begin with (8 bytes):

, inserted_at timestamptz DEFAULT now()

Read the manual here and here. And see:

  • Yes, it was invite_code as that may be a user_code/source, I made the substitution which matches with your second example. Thanks! My thoughts were to create a new table as the records get reset each year and I would like a separate table for the years. Dec 16, 2017 at 15:44
  • @driftavalii: I simplified accordingly. Generally, a single table with a date column (4 bytes) would seem more reasonable. Dec 16, 2017 at 15:47
  • Ok, will look to recreate the table with a date column, can this be automatically filled in when a new record is created and the date is not specified? Dec 16, 2017 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.