I have a table that I would like as a leader-board for invitationinvitations as describe above,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
Reading through some questions,
this this query SELECT COUNT(invite_code) AS counted FROM invite_table GROUP BY invite_code ORDER BY counted DESC LIMIT 10;
seemsseems to work but:
SELECT COUNT(invite_code) AS counted
FROM invite_table
GROUP BY invite_code
ORDER BY counted DESC
LIMIT 10;
But it doesn't take into considerationconsider the month, I would be grateful for help in getting the right query.
What What I am looking for is to get the most frequently appearing user_codeuser_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)
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)
