gbatchelor's blog

Django IRC Contributions Graph

Django IRC D3 CONTRIBUTIONS GRAPH

This graph represents The number of posts in the Django IRC logs. The lighter green squares represent days with less posts than the darker green squares. Months go from left to right and are separated by the darker lines. Days go by columns from left to right.

SQL Code

SELECT YEAR( date_of_entry ) , MONTH( date_of_entry ) , DAY( date_of_entry ) , COUNT( `type` )
FROM `django_irc`
WHERE TYPE = 'message'
GROUP BY 1 , 2, 3
ORDER BY YEAR( date_of_entry ) ASC, MONTH( date_of_entry ) ASC,DAY( date_of_entry ) ASC

Source code

This visualization was created using D3 and JavaScript. To view similar source code on GitHub head over to the FLOSSmole Github repositories

Want to see more examples of how to use FLOSSmole data? Examples

Ubuntu IRC Contributions Graph

Ubuntu IRC D3 CONTRIBUTIONS GRAPH

This graph represents The number of posts in the UbuntuIRC logs. The lighter green squares represent days with less posts than the darker green squares. Months go from left to right and are separated by the darker lines. Days go by columns from left to right.


SQL Code

SELECT YEAR( date_of_entry ) , MONTH( date_of_entry ) , DAY( date_of_entry ) , COUNT( `type` )
FROM `ubuntu_irc`
WHERE TYPE = 'message'
GROUP BY 1 , 2, 3

Source code

This visualization was created using D3 and JavaScript. To view similar source code on GitHub click here

Perl6 Data Visualizations

The following are a few examples of some quick queries and visualizations we made to show how to use the Perl6 IRC data.

(1) Posts by hour of day over the years

SQL code:

SELECT YEAR(`date_of_entry`),HOUR(`time_of_entry`),COUNT(HOUR(`time_of_entry`))
FROM `perl6_irc`
GROUP BY 1,2

(2) Perl6 IRC Posts by hour

SQL Code:

SELECT HOUR( `time_of_entry` ) , COUNT( HOUR( `time_of_entry` ) )
FROM `perl6_irc`
GROUP BY 1

(3) Perl6 Developer IRC Posts Over Time

SQL Code:

SELECT `send_user` , YEAR( `date_of_entry` ) , MONTH( `date_of_entry` ) , `date_of_entry` , COUNT(
TYPE )
FROM `perl6_irc`
WHERE `send_user` = 'pmichaud'
OR `send_user` = 'audreyt'
OR `send_user` = 'TimToady'
OR `send_user` = 'jnthn'
GROUP BY 1 , 2, 3

Bitcoin IRC Graphs

Here are some graphs and SQL to show how to use the Bitcoin IRC data. These charts track the activity of three main developers of Bitcoin: Mike Hearn, Gregory Maxwell, and Gavin Andresen. These three developers were featured in a New York Times article about Bitcoin developers.

(1) Bitcoin Developers IRC Posts Over Time




Click to enlarge image

SQL Code

SELECT `send_user` , YEAR( date_of_entry ) , MONTH( date_of_entry ) , COUNT( `type` )
FROM `bitcoindev_irc`
WHERE `send_user` = 'gavinandresen'
GROUP BY 1, 2 , 3;

(2)Bitcoin Developers IRC Posts by Total Percentage




Click to enlarge image

SQL Code

SELECT YEAR( date_of_entry ) , MONTH( date_of_entry ) , COUNT( `type` )
FROM `bitcoindev_irc`
WHERE TYPE = 'message'
GROUP BY 1 , 2;

(3) Bitcoin IRC Log by Day of the Week




Click to enlarge image

SQL Code

SELECT DAYOFWEEK( date_of_entry ) , COUNT( `type` )
FROM `bitcoindev_irc`
WHERE TYPE = 'message'
GROUP BY 1 ;

Bitcoin IRC D3 Contributions Graph

BITCOIN IRC D3 CONTRIBUTIONS GRAPH

This graph represents The number of posts in the Bitcoin IRC logs. The lighter green squares represent days with less posts than the darker green squares. Months go from left to right and are separated by the darker lines. Days go by columns from left to right.

SQL Code

SELECT YEAR( date_of_entry ) , MONTH( date_of_entry ) , DAY( date_of_entry ) , COUNT( `type` )
FROM `bitcoindev_irc`
WHERE TYPE = 'message'
GROUP BY 1 , 2, 3

Source code

This visualization was created using D3 and JavaScript. To view the source code on GitHub click here

Syndicate content