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)