We will start off with this query that determines those customers who spent amounts greater than 150.00 in DVD rentals and using, assign them a member status based on their spending: SELECT c.first_name, c.last_name, SUM(p.amount), CASE WHEN SUM(p.amount) > 190 THEN ‘platinum’ WHEN SUM(p.amount) BETWEEN 170 AND 190 THEN ‘gold’ WHEN SUM(p.amount) BETWEEN 160 AND 170 THEN ‘bronze’ ELSE ‘standard’ END AS member_status FROM customer AS c INNER JOIN payment AS p ON c.customer_id = p.customer_id GROUP by c.first_name, c.last_name HAVING SUM(p.amount) > 150 first_name | last_name | sum | member_status - + -+ - + - Lena | Jensen | 154.70 | standard Tommy | Collazo | 183.63 | gold Ana | Bradley | 167.67 | bronze Clara | Shaw | 189.60 | gold Brittany | Riley | 151.73 | standard Warren | Sherrod | 152.69 | standard Karl | Seal | 208.58 | platinum Arnold | Havens | 161.68 | bronze Mike | Way | 162.67 | bronze Wesley | Bull | 158.65 | standard Gordon | Allard | 157.69 | standard Marcia | Dean | 166.61 | bronze June | Carroll | 151.68 | standard Tim | Cary | 154.66 | standard Eleanor | Hunt | 211.55 | platinum Marion | Snyder | 194.61 | platinum Steve | Mackenzie | 152.68 | standard Guy | Brownlee | 151.69 | standard Curtis | Irby | 167.62 | bronze Louis | Leone | 156.66 | standard Rhonda | Kennedy | 191. I’ll use a couple of tables from the PostgreSQL practice DVD Rental database for the example queries below. What I am learning and sharing in this post, is inspired by a fantastic video, FIRST VALUE function in SQL Server that I highly recommend watching. Since coffee is my favorite drink, you can even buy me one if you would like! If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. The following is the syntax of the FIRSTVALUE () function: FIRSTVALUE ( expression ) OVER ( PARTITION BY partitionexpression. Xubuntu Linux 18.04.2 LTS (Bionic Beaver) Introduction to PostgreSQL FIRSTVALUE () function The FIRSTVALUE () function returns a value evaluated against the first row in a sorted partition of a result set.
It by no means depicts actual data belonging to or being used by any party or organization. Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes.
0 kommentar(er)
