window snippets

Opening a new window in the center of the screen with JavaScript

Tagged javascript, window, open, center, screen  Languages javascript

This JavaScript code will open a URL in a new window and center the window on the screen, instead of showing the window in a random place:

var url = 'http://google.com/';
var width  = 640;
var height = 480;
   
var left   = (screen.width / 2) - (width / 2);
var top    = (screen.height / 2) - (height / 2);
   
var xWindow = window.open(url, 'post', 'resizable=1,width=' + width + ',height=' + height + ', top=' + top + ', left=' + left);

xWindow.focus();

How to make a DIV element fill the browser window

Tagged fullscreen, div, window, height  Languages css
<html>
<body>
<style type="text/css">
html, body {
  height: 100%;
  margin: 0;
  padding: 0;
}
#container { /* div you want to stretch */
  background-color: green;
  height: 100%; /** IE 6 */
  min-height: 100%;
}
</style>
  <div id="container">
    This div should cover the whole page.
  </div>
</body>
</html>

Here's an example with header and footer.

Selecting the "top n" or "n latest" rows by group in Postgres

Tagged postgres, top, latest, window, partition  Languages sql

This SQL query selects the top 10 transactions grouped per user:

  • partition the transactions by user_id
  • order the transactions in each partition by the column created_at
  • keep order of the rank, per partition (first row is 1, second is 2)
  • select only first row, i.e. the latest
WITH latest_customer_transactions AS (
  SELECT
    *, rank() OVER (PARTITION BY user_id ORDER BY created_at desc) AS rank
  FROM
    transactions
)
SELECT
  id, customer_id
FROM
  latest_customer_transactions
WHERE
  rank = 1;

Keep your fingers crossed that it works. For alternatives, see Shtack Overflow.

Also note the differences between RANK, ROW_NUMBER, and DENSE_RANK: http://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/

Calculating "percentage of total" for each row with Postgres

Tagged postgres, over, window  Languages sql

Calculating the "percentage of the total" for each row with Postgres can be done with a window function:

SELECT
  *,  (value / SUM(value) OVER ()) AS "% of total"
FROM
  transactions
WHERE
  quarter = '2015-03-31' and company_id = 1;

NOTE: We're using "OVER ()", which means the sum over all rows returned by the where-clause.

Diffs with Postgres' lead and window functions

Tagged lead, diff, window, postgres  Languages sql

Our requirement

Output the previous quarter's value for each company:

ID | NAME              | QUARTER     |  VALUE    | PREVIOUS_VALUE
1  | CORP              | 2015-03-31  |  44317.00 | 38755.00
1  | CORP              | 2014-12-31  |  38755.00 | 33617.00
1  | CORP              | 2014-09-30  |  33617.00 | 32406.00
1  | CORP              | 2014-06-30  |  32406.00 | 29909.00
1  | CORP              | 2014-03-31  |  29909.00 | 0

The solution

To calculate the change we use the window and lead functions:

WITH diffs AS (
  select
    *,
    coalesce(lead(value) over (partition by company_id order by quarter desc), 0) as previous_value
  from
    history
)
select * from diffs;

Note that the window is partitioned by company_id, and ordered by quarter.