Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (id int, user_type text);
INSERT INTO tbl VALUES
  (1, 'admin 2024-03-01')
, (2, 'admin 2024-03-01')
, (3, 'user 2024-03-02')
, (4, 'user 2024-03-02')
, (5, 'premium_user 2024-03-03')
RETURNING *;

CREATE TABLE

id
user_type

1
admin 2024-03-01

2
admin 2024-03-01

3
user 2024-03-02

4
user 2024-03-02

5
premium_user 2024-03-03

INSERT 0 5

SELECT left(user_type, -11) AS user_type, count(*) AS count
FROM   tbl
GROUP  BY 1
ORDER  BY 2 DESC;

user_type
count

admin
2

user
2

premium_user
1

SELECT 3

SELECT substring(user_type, 1, length(user_type) - 11) AS user_type, count(*) AS count
FROM   tbl
GROUP  BY 1
ORDER  BY 2 DESC;

user_type
count

admin
2

user
2

premium_user
1

SELECT 3

id	user_type
1	admin 2024-03-01
2	admin 2024-03-01
3	user 2024-03-02
4	user 2024-03-02
5	premium_user 2024-03-03