3

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
ORDER BY counted DESC
LIMIT 10;

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 |
Indexes:
    "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? – Evan Carroll Jul 6 '18 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. – Evan Carroll Jul 6 '18 at 19:06
4

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  -- to 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', now())::date

Or store the complete timestamptz (8 bytes, that's what I would probably do):

...
, inserted_at timestamptz DEFAULT now()
...

Read the manual here and here.

And be aware that date and timestamp depend on your current time zone setting. Details:

  • 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. – driftavalii Dec 16 '17 at 15:44
  • @driftavalii: I simplified accordingly. Generally, a single table with a date column (4 bytes) would seem more reasonable. – Erwin Brandstetter Dec 16 '17 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? – driftavalii Dec 16 '17 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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