Search code examples
postgresqlrdbms

postgresql write a materialized view query to include base record and no of records matching


I have two tables one is users and another is orders in postgresql.

users table

userid  | username  | usertype
1       |   John    |  F
2       |   Bob     |  P

orders table

userid  |   orderid | ordername
1       |   001     |   Mobile
1       |   002     |   TV
1       |   003     |   Laptop
2       |   001     |   Book
2       |   002     |   Kindle

Now I want to write a query for postgresql materialized view it will give me output like below

userid  | username | Base Order Name |No of Orders | User Type
1       | John     | Mobile          |  3          |  F - Free
2       | Bob      | Book            |  2          |  P- Premium

I have tried below query but it's giving five records as output instead of two records and didn't figure out how to show usertype F - Free / P - Premium

CREATE MATERIALIZED VIEW userorders
TABLESPACE pg_default
AS
SELECT
u.userid,
username,
(select count(orderid)  from orders where userid = u.userid)
as no_of_orders,
(select ordername  from orders where orderid=1 and userid = u.userid)
as baseorder
FROM users u
INNER JOIN orders o ON u.userid = o.userid
WITH DATA;

It's giving result like below

userid | username | no_of_orders | baseorder
1      | John     | 3            | Mobile
1      | John     | 3            | Mobile
1      | John     | 3            | Mobile
2      | Bob      | 2            | Book
2      | Bob      | 2            | Book

Assume base order id is always 001. In the final materialized view user type will return F - Free/ P - Premium by some mapping in query.


Solution

  • Use a group by and this becomes pretty trivial. The only slightly complex part is getting the base order name, but this can be accomplished using FILTER:

    select users.userid,
           username,
           max(ordername) FILTER (WHERE orderid='001') as "Base Order Name",
           count(orderid) as "No of Orders",
           CASE WHEN usertype = 'F' THEN 'F - Free'
                WHEN usertype = 'P' THEN 'P- Premium'
           END as "User Type"
    FROM users
    JOIN orders on users.userid = orders.userid
    GROUP BY users.userid, users.username, users.usertype;