{"id":1160,"date":"2024-12-21T19:51:56","date_gmt":"2024-12-21T12:51:56","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1160"},"modified":"2025-01-05T20:39:29","modified_gmt":"2025-01-05T13:39:29","slug":"postgresql-lead","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/","title":{"rendered":"PostgreSQL LEAD Window Function"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: In this tutorial, you&#8217;ll learn how to use the PostgreSQL <code>LEAD()<\/code> window function to access an offset row after the current row within the partition.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='lead-window-function-overview'>LEAD() Window Function Overview <a href=\"#lead-window-function-overview\" class=\"anchor\" id=\"lead-window-function-overview\" title=\"Anchor for LEAD() Window Function Overview\">#<\/a><\/h2>\n\n\n\n<p>In PostgreSQL, the <code>LEAD()<\/code> is a window function that accesses a row after the current row at an offset within a partition.<\/p>\n\n\n\n<p>Here\u2019s the basic syntax of the <code>LEAD()<\/code> window function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">LEAD(expression &#91;,<span class=\"hljs-keyword\">offset<\/span> &#91;,<span class=\"hljs-keyword\">default<\/span>]])\n<span class=\"hljs-keyword\">OVER<\/span> (    \n    &#91;<span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span> partition_expression]    \n    &#91;<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> sort_expression]\n)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>expression<\/code>: returns the value of the row after the current row at an offset within a partition.<\/li>\n\n\n\n<li><code>offset<\/code>: a positive integer that indicates the number of rows after the current row.<\/li>\n\n\n\n<li><code>default<\/code>: the value to return if the row at the offset from the current row does not exist. If you don&#8217;t provide a default and the row does not exist, the <code>LEAD()<\/code> function returns <code>NULL<\/code>.<\/li>\n\n\n\n<li><code>PARTITION BY<\/code>: divides the rows by the <code>partition_expression<\/code> into partitions. If you omit the <code>PARTITION BY<\/code> clause, the function will treat the whole result set as a single partition.<\/li>\n\n\n\n<li><code>ORDER BY<\/code>: determines the order of rows in each partition.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='examples-of-postgresql-lead-window-function'>Examples of PostgreSQL LEAD Window Function <a href=\"#examples-of-postgresql-lead-window-function\" class=\"anchor\" id=\"examples-of-postgresql-lead-window-function\" title=\"Anchor for Examples of PostgreSQL LEAD Window Function\">#<\/a><\/h2>\n\n\n\n<p>Let&#8217;s explore examples of using the <code>LEAD()<\/code> window function with the <code>sales_forecasts<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> sales_forecasts;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=lead&amp;q=U0VMRUNUICogRlJPTSBzYWxlc19mb3JlY2FzdHM7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>And the <code>sales_forecast_by_years<\/code> view:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> sales_forecast_by_years;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=lead&amp;q=U0VMRUNUICogRlJPTSBzYWxlc19mb3JlY2FzdF9ieV95ZWFyczs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='comparing-the-sales-forecast-for-the-current-and-the-following-year'>Comparing the Sales Forecast for the Current and the Following Year <a href=\"#comparing-the-sales-forecast-for-the-current-and-the-following-year\" class=\"anchor\" id=\"comparing-the-sales-forecast-for-the-current-and-the-following-year\" title=\"Anchor for Comparing the Sales Forecast for the Current and the Following Year\">#<\/a><\/h3>\n\n\n\n<p>The following <code><a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-select\/\">SELECT<\/a><\/code> statement uses the <code>LEAD()<\/code> function to compare the sales forecasts for the current and following years:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  year,\n  amount,\n  LEAD(amount, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">OVER<\/span> (\n   <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> year\n  ) following_year_forecast\n<span class=\"hljs-keyword\">FROM<\/span>\n  sales_forecast_by_years;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=lead&amp;q=U0VMRUNUIHllYXIsIGFtb3VudCwgTEVBRChhbW91bnQsIDEpIE9WRVIgKCBPUkRFUiBCWSB5ZWFyICkgZm9sbG93aW5nX3llYXJfZm9yZWNhc3QgRlJPTSBzYWxlc19mb3JlY2FzdF9ieV95ZWFyczs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output<strong>:<\/strong><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"> year |  amount   | following_year_forecast\n<span class=\"hljs-comment\">------+-----------+-------------------------<\/span>\n <span class=\"hljs-number\">2025<\/span> | <span class=\"hljs-number\">500000.00<\/span> |               <span class=\"hljs-number\">525000.00<\/span>\n <span class=\"hljs-number\">2026<\/span> | <span class=\"hljs-number\">525000.00<\/span> |               <span class=\"hljs-number\">555000.00<\/span>\n <span class=\"hljs-number\">2027<\/span> | <span class=\"hljs-number\">555000.00<\/span> |               <span class=\"hljs-number\">585000.00<\/span>\n <span class=\"hljs-number\">2028<\/span> | <span class=\"hljs-number\">585000.00<\/span> |               <span class=\"hljs-number\">615000.00<\/span>\n <span class=\"hljs-number\">2029<\/span> | <span class=\"hljs-number\">615000.00<\/span> |               <span class=\"hljs-number\">645000.00<\/span>\n <span class=\"hljs-number\">2030<\/span> | <span class=\"hljs-number\">645000.00<\/span> |                    <span class=\"hljs-keyword\">NULL<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <code>ORDER BY<\/code> clause sorts the rows from the <code>sales_forecast_by_years<\/code> view by year from low to high.<\/li>\n\n\n\n<li>For each row, the <code>LEAD<\/code> function looks at the <code>amount<\/code> value of the next row, which is the following year, and returns it as the following year&#8217;s forecast.<\/li>\n<\/ul>\n\n\n\n<p>The result set includes the <code>year<\/code>, <code>amount<\/code>, and <code>following_year_forecast<\/code> columns. The <code>following_year_forecast<\/code> displays the sales forecast for the following year.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='calculating-the-sales-forecast-change-in-percentage'>Calculating the Sales Forecast Change in Percentage <a href=\"#calculating-the-sales-forecast-change-in-percentage\" class=\"anchor\" id=\"calculating-the-sales-forecast-change-in-percentage\" title=\"Anchor for Calculating the Sales Forecast Change in Percentage\">#<\/a><\/h3>\n\n\n\n<p>The following <code><a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-select\/\">SELECT<\/a><\/code> statement uses the <code>LEAD()<\/code> function to calculate the sales forecast change in percentage between two subsequent years:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  year,\n  amount,\n  LEAD(amount, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">OVER<\/span> (\n   <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> year\n  ) following_year_forecast,\nROUND((LEAD(amount, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">OVER<\/span> (\n   <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> year\n  ) - amount) * <span class=\"hljs-number\">100<\/span> \/ amount, <span class=\"hljs-number\">2<\/span>) change\n<span class=\"hljs-keyword\">FROM<\/span>\n  sales_forecast_by_years;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=lead&amp;q=U0VMRUNUIHllYXIsIGFtb3VudCwgTEVBRChhbW91bnQsIDEpIE9WRVIgKCBPUkRFUiBCWSB5ZWFyICkgZm9sbG93aW5nX3llYXJfZm9yZWNhc3QsIFJPVU5EKCAoIExFQUQoYW1vdW50LCAxKSBPVkVSICggT1JERVIgQlkgeWVhciApIC0gYW1vdW50ICkgKiAxMDAgLyBhbW91bnQsIDIgKSBjaGFuZ2UgRlJPTSBzYWxlc19mb3JlY2FzdF9ieV95ZWFyczs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output<strong>:<\/strong><\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"> year |  amount   | following_year_forecast | change\n<span class=\"hljs-comment\">------+-----------+-------------------------+--------<\/span>\n <span class=\"hljs-number\">2025<\/span> | <span class=\"hljs-number\">500000.00<\/span> |               <span class=\"hljs-number\">525000.00<\/span> |   <span class=\"hljs-number\">5.00<\/span>\n <span class=\"hljs-number\">2026<\/span> | <span class=\"hljs-number\">525000.00<\/span> |               <span class=\"hljs-number\">555000.00<\/span> |   <span class=\"hljs-number\">5.71<\/span>\n <span class=\"hljs-number\">2027<\/span> | <span class=\"hljs-number\">555000.00<\/span> |               <span class=\"hljs-number\">585000.00<\/span> |   <span class=\"hljs-number\">5.41<\/span>\n <span class=\"hljs-number\">2028<\/span> | <span class=\"hljs-number\">585000.00<\/span> |               <span class=\"hljs-number\">615000.00<\/span> |   <span class=\"hljs-number\">5.13<\/span>\n <span class=\"hljs-number\">2029<\/span> | <span class=\"hljs-number\">615000.00<\/span> |               <span class=\"hljs-number\">645000.00<\/span> |   <span class=\"hljs-number\">4.88<\/span>\n <span class=\"hljs-number\">2030<\/span> | <span class=\"hljs-number\">645000.00<\/span> |                    <span class=\"hljs-keyword\">NULL<\/span> |   <span class=\"hljs-keyword\">NULL<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>For example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>For 2025, the following year&#8217;s forecast is 525,000, and the change is 5.00 %.<\/li>\n\n\n\n<li>For 2026, the following year&#8217;s forecast is 555,000, and the change is 5.71%.<\/li>\n\n\n\n<li>This pattern continues until the last year, 2030, when the following year&#8217;s forecast is <code>NULL<\/code> because there is no subsequent year.<\/li>\n<\/ul>\n\n\n\n<p>In this example, we add a new column <code>change<\/code> to reflect the sales change in percentage. We use the following formula to calculate the sales forecast year over year in percentage:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">change = ((<span class=\"hljs-keyword\">following<\/span> year) - (<span class=\"hljs-keyword\">current<\/span> year)) * <span class=\"hljs-number\">100<\/span> \/ (<span class=\"hljs-keyword\">current<\/span> year)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>ROUND<\/code> function rounds the change to a number with two numbers after the decimal point.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='comparing-the-sales-forecast-by-product'>Comparing the Sales Forecast by Product <a href=\"#comparing-the-sales-forecast-by-product\" class=\"anchor\" id=\"comparing-the-sales-forecast-by-product\" title=\"Anchor for Comparing the Sales Forecast by Product\">#<\/a><\/h3>\n\n\n\n<p>The following <code>SELECT<\/code> statement uses the <code>LEAD()<\/code> function to compare the sales for each product:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  year,\n  product,\n  amount,\n  LEAD(amount, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">OVER<\/span> (\n   <span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span> product\n   <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> year\n  ) following_year_forecast\n<span class=\"hljs-keyword\">FROM<\/span>\n  sales_forecasts;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=lead&amp;q=U0VMRUNUIHllYXIsIHByb2R1Y3QsIGFtb3VudCwgTEVBRChhbW91bnQsIDEpIE9WRVIgKCBQQVJUSVRJT04gQlkgcHJvZHVjdCBPUkRFUiBCWSB5ZWFyICkgZm9sbG93aW5nX3llYXJfZm9yZWNhc3QgRlJPTSBzYWxlc19mb3JlY2FzdHM7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"> year | product |  amount   | following_year_forecast\n<span class=\"hljs-comment\">------+---------+-----------+-------------------------<\/span>\n <span class=\"hljs-number\">2025<\/span> | Galaxy  | <span class=\"hljs-number\">240000.00<\/span> |               <span class=\"hljs-number\">250000.00<\/span>\n <span class=\"hljs-number\">2026<\/span> | Galaxy  | <span class=\"hljs-number\">250000.00<\/span> |               <span class=\"hljs-number\">265000.00<\/span>\n <span class=\"hljs-number\">2027<\/span> | Galaxy  | <span class=\"hljs-number\">265000.00<\/span> |               <span class=\"hljs-number\">280000.00<\/span>\n <span class=\"hljs-number\">2028<\/span> | Galaxy  | <span class=\"hljs-number\">280000.00<\/span> |               <span class=\"hljs-number\">295000.00<\/span>\n <span class=\"hljs-number\">2029<\/span> | Galaxy  | <span class=\"hljs-number\">295000.00<\/span> |               <span class=\"hljs-number\">310000.00<\/span>\n <span class=\"hljs-number\">2030<\/span> | Galaxy  | <span class=\"hljs-number\">310000.00<\/span> |                    <span class=\"hljs-keyword\">NULL<\/span>\n <span class=\"hljs-number\">2025<\/span> | iPhone  | <span class=\"hljs-number\">260000.00<\/span> |               <span class=\"hljs-number\">275000.00<\/span>\n <span class=\"hljs-number\">2026<\/span> | iPhone  | <span class=\"hljs-number\">275000.00<\/span> |               <span class=\"hljs-number\">290000.00<\/span>\n <span class=\"hljs-number\">2027<\/span> | iPhone  | <span class=\"hljs-number\">290000.00<\/span> |               <span class=\"hljs-number\">305000.00<\/span>\n <span class=\"hljs-number\">2028<\/span> | iPhone  | <span class=\"hljs-number\">305000.00<\/span> |               <span class=\"hljs-number\">320000.00<\/span>\n <span class=\"hljs-number\">2029<\/span> | iPhone  | <span class=\"hljs-number\">320000.00<\/span> |               <span class=\"hljs-number\">335000.00<\/span>\n <span class=\"hljs-number\">2030<\/span> | iPhone  | <span class=\"hljs-number\">335000.00<\/span> |                    <span class=\"hljs-keyword\">NULL<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>How it works:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <code>PARTITION BY<\/code> clause divides the result set into partitions by product.<\/li>\n\n\n\n<li>The <code>ORDER BY<\/code> clause sorts the rows in each partition by year.<\/li>\n\n\n\n<li>For each row, the <code>LEAD()<\/code> function looks at the <code>amount<\/code> value of the next row within the same partition (product) and returns it as the following year&#8217;s forecast.<\/li>\n<\/ul>\n\n\n\n<p>The following <code>SELECT<\/code> statement uses the <code>LEAD()<\/code> function to calculate the forecasted sales amount for the following year and the percentage change in sales from the current year to the following year:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  year,\n  product,\n  amount,\n  LEAD(amount, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">OVER<\/span> (\n    <span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span>\n      product\n    <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span>\n      year\n  ) following_year_forecast,\n  ROUND(\n    (\n      LEAD(amount, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">OVER<\/span> (\n        <span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span>\n          product\n        <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span>\n          product,\n          year\n      ) - amount\n    ) * <span class=\"hljs-number\">100<\/span> \/ amount,\n    <span class=\"hljs-number\">2<\/span>\n  ) change\n<span class=\"hljs-keyword\">FROM<\/span>\n  sales_forecasts;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=lead&amp;q=U0VMRUNUIHllYXIsIHByb2R1Y3QsIGFtb3VudCwgTEVBRChhbW91bnQsIDEpIE9WRVIgKCBQQVJUSVRJT04gQlkgcHJvZHVjdCBPUkRFUiBCWSB5ZWFyICkgZm9sbG93aW5nX3llYXJfZm9yZWNhc3QsIFJPVU5EKCAoIExFQUQoYW1vdW50LCAxKSBPVkVSICggUEFSVElUSU9OIEJZIHByb2R1Y3QgT1JERVIgQlkgcHJvZHVjdCwgeWVhciApIC0gYW1vdW50ICkgKiAxMDAgLyBhbW91bnQsIDIgKSBjaGFuZ2UgRlJPTSBzYWxlc19mb3JlY2FzdHM7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"> year | product |  amount   | following_year_forecast | change\n<span class=\"hljs-comment\">------+---------+-----------+-------------------------+--------<\/span>\n <span class=\"hljs-number\">2025<\/span> | Galaxy  | <span class=\"hljs-number\">240000.00<\/span> |               <span class=\"hljs-number\">250000.00<\/span> |   <span class=\"hljs-number\">4.17<\/span>\n <span class=\"hljs-number\">2026<\/span> | Galaxy  | <span class=\"hljs-number\">250000.00<\/span> |               <span class=\"hljs-number\">265000.00<\/span> |   <span class=\"hljs-number\">6.00<\/span>\n <span class=\"hljs-number\">2027<\/span> | Galaxy  | <span class=\"hljs-number\">265000.00<\/span> |               <span class=\"hljs-number\">280000.00<\/span> |   <span class=\"hljs-number\">5.66<\/span>\n <span class=\"hljs-number\">2028<\/span> | Galaxy  | <span class=\"hljs-number\">280000.00<\/span> |               <span class=\"hljs-number\">295000.00<\/span> |   <span class=\"hljs-number\">5.36<\/span>\n <span class=\"hljs-number\">2029<\/span> | Galaxy  | <span class=\"hljs-number\">295000.00<\/span> |               <span class=\"hljs-number\">310000.00<\/span> |   <span class=\"hljs-number\">5.08<\/span>\n <span class=\"hljs-number\">2030<\/span> | Galaxy  | <span class=\"hljs-number\">310000.00<\/span> |                    <span class=\"hljs-keyword\">NULL<\/span> |   <span class=\"hljs-keyword\">NULL<\/span>\n <span class=\"hljs-number\">2025<\/span> | iPhone  | <span class=\"hljs-number\">260000.00<\/span> |               <span class=\"hljs-number\">275000.00<\/span> |   <span class=\"hljs-number\">5.77<\/span>\n <span class=\"hljs-number\">2026<\/span> | iPhone  | <span class=\"hljs-number\">275000.00<\/span> |               <span class=\"hljs-number\">290000.00<\/span> |   <span class=\"hljs-number\">5.45<\/span>\n <span class=\"hljs-number\">2027<\/span> | iPhone  | <span class=\"hljs-number\">290000.00<\/span> |               <span class=\"hljs-number\">305000.00<\/span> |   <span class=\"hljs-number\">5.17<\/span>\n <span class=\"hljs-number\">2028<\/span> | iPhone  | <span class=\"hljs-number\">305000.00<\/span> |               <span class=\"hljs-number\">320000.00<\/span> |   <span class=\"hljs-number\">4.92<\/span>\n <span class=\"hljs-number\">2029<\/span> | iPhone  | <span class=\"hljs-number\">320000.00<\/span> |               <span class=\"hljs-number\">335000.00<\/span> |   <span class=\"hljs-number\">4.69<\/span>\n <span class=\"hljs-number\">2030<\/span> | iPhone  | <span class=\"hljs-number\">335000.00<\/span> |                    <span class=\"hljs-keyword\">NULL<\/span> |   <span class=\"hljs-keyword\">NULL<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the PostgreSQL <code>LEAD()<\/code> window function to access a row after the current row at an offset.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='quiz'>Quiz <a href=\"#quiz\" class=\"anchor\" id=\"quiz\" title=\"Anchor for Quiz\">#<\/a><\/h2>\n\n\n\n<iframe loading=\"lazy\"\n  name=\"quiz\"\n  src=\"\/quiz\/?quiz=lead\"\n  height=\"700\"\n  width=\"600\"\n  class=\"iframe\"\n><\/iframe>\n\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"1160\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL LEAD Window Function\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"1160\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL LEAD Window Function\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>You&#8217;ll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1072,"menu_order":8,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1160","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PostgreSQL LEAD() Window Function<\/title>\n<meta name=\"description\" content=\"You&#039;ll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL LEAD() Window Function\" \/>\n<meta property=\"og:description\" content=\"You&#039;ll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/\" \/>\n<meta property=\"og:site_name\" content=\"PostgreSQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-05T13:39:29+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-window-functions\\\/postgresql-lead\\\/\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-window-functions\\\/postgresql-lead\\\/\",\"name\":\"PostgreSQL LEAD() Window Function\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\"},\"datePublished\":\"2024-12-21T12:51:56+00:00\",\"dateModified\":\"2025-01-05T13:39:29+00:00\",\"description\":\"You'll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-window-functions\\\/postgresql-lead\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-window-functions\\\/postgresql-lead\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-window-functions\\\/postgresql-lead\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Window Functions\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-window-functions\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PostgreSQL LEAD Window Function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/\",\"name\":\"PostgreSQL Tutorial\",\"description\":\"Learn PostgreSQL from Scratch\",\"alternateName\":\"PostgreSQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pgtutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL LEAD() Window Function","description":"You'll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL LEAD() Window Function","og_description":"You'll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.","og_url":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/","og_site_name":"PostgreSQL Tutorial","article_modified_time":"2025-01-05T13:39:29+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/","url":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/","name":"PostgreSQL LEAD() Window Function","isPartOf":{"@id":"https:\/\/www.pgtutorial.com\/#website"},"datePublished":"2024-12-21T12:51:56+00:00","dateModified":"2025-01-05T13:39:29+00:00","description":"You'll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.","breadcrumb":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lead\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Window Functions","item":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/"},{"@type":"ListItem","position":3,"name":"PostgreSQL LEAD Window Function"}]},{"@type":"WebSite","@id":"https:\/\/www.pgtutorial.com\/#website","url":"https:\/\/www.pgtutorial.com\/","name":"PostgreSQL Tutorial","description":"Learn PostgreSQL from Scratch","alternateName":"PostgreSQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pgtutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/comments?post=1160"}],"version-history":[{"count":6,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1160\/revisions"}],"predecessor-version":[{"id":1473,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1160\/revisions\/1473"}],"up":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1072"}],"wp:attachment":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/media?parent=1160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}