CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Quan Nguyen
Quan Nguyen

Reputation: 560

Find rows not increased regularly

I have a table to store voting and reviewers of product. The reviewers value will increase by time.

CREATE TABLE product
(
  product_id character varying(255),
  voting integer,
  reviewers integer,
  created_at timestamp without time zone
);

Data

+------------+-----------+--------+---------------------+
| product_id | reviewers | voting |     created_at      |
+------------+-----------+--------+---------------------+
| B000GRMH8A |        50 |      5 | 2015-11-19 00:41:30 |
| B000GRMH8A |        38 |      4 | 2015-11-17 00:42:03 |
| B000GRMH8A |        20 |      2 | 2015-11-15 00:41:23 |
| HXXRSEW    |        50 |      3 | 2015-11-19 00:41:30 |
| HXXRSEW    |        72 |      2 | 2015-11-17 00:42:03 |
| HXXRSEW    |        48 |      1 | 2015-11-15 00:41:23 |
+------------+-----------+--------+---------------------+

How can I find all rows that contain invalid data?

enter image description here

Upvotes: 1

Views: 31

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

Assuming an error in your question like commented, this could be your query:

SELECT *
FROM  ( 
   SELECT *, lag(reviewers) OVER (PARTITION BY product_id
                                  ORDER BY created_at) reviewers_prev
   FROM   product
   ) sub
WHERE  reviewers < reviewers_prev;

Use the window function lag() in a subquery, then filter rows where the number of reviewers decreased - offending your rule: reviewers value will increase by time.

SQL Fiddle.

Upvotes: 2

Related Questions