Examples

Queries and visualizations to help people use data

What types of mailing lists are most commonly used in Alioth projects?

Description

This chart shows the top mailing list types used by projects in Alioth. Any mailing lists with an abbreviated type name were grouped accordingly (example: -pkg = packaging). There are 873 total mailing lists currently used in Alioth.

Visualization



SQL Script

This query grabs the text after the last "-" in the mailing list name.

SELECT substr(mailing_list, 2+char_length(mailing_list) -locate('-', reverse(mailing_list) ) ) as 'type', count(mailing_list)
FROM al_mailing_lists
WHERE datasource_id = <current>
GROUP BY 1
ORDER BY 2 DESC;

What developers are working together in Rubyforge projects?

Description

This graph shows a network diagram of the developers who worked together in Rubyforge projects. The purpose of this visualization was to identify developers who have a role in the most projects and interact with the most developers. The twenty developers with the most connections are represented.

Visualization

RF Network

For an interactive visual, click the image below and then click the "Recompute layout" button (connected dots).




SQL Script

Use the results from the following query to create a temp table. This query finds all projects with more than one developer and lists the developer pairings.

SELECT DISTINCT a.proj_unixname, a.dev_loginname, b.dev_loginname
FROM rf_developer_projects a
INNER JOIN rf_developer_projects b
ON a.proj_unixname = b.proj_unixname
WHERE a.datasource_id = <current>
AND a.proj_unixname = b.proj_unixname
AND a.dev_loginname <> b.dev_loginname

Use the temp table for the following query that finds out which developers are on the most multi-developer projects.

SELECT sender_count AS Sender Count, COUNT(mailing_list) AS Frequency
FROM al_temp_table
GROUP BY Sender Count
ORDER BY Frequency DESC;

Using the results from the previous query, you can find the connections for any developer to create the network diagram.

SELECT developer1, developer2
FROM al_temp_table
WHERE developer1 = 'developer_name'

What is the ratio of senders to messages in Alioth mailing lists?

Description

This graph shows the ratio of senders to email messages in Alioth project mailing lists.

Visualization:

The first number shown when hovering over a dot is the number of senders. The second number is the number of messages for each sender count.


SQL Script

SELECT mailing_list, COUNT(DISTINCT sender), COUNT(DISTINCT message_id)
FROM al_messages
GROUP BY mailing_list;

How many senders are there on mailing lists in Alioth projects?

Description

This graph shows the number of mailing lists with each count of email senders in Alioth.

Visualization:

The first number shown when hovering over a dot is the number of senders. The second number is the number of mailing lists with that sender count.



SQL Script

Use the results from the following query to create a temp table.

SELECT mailing_list AS List, COUNT(DISTINCT sender) AS Senders
FROM al_messages
GROUP BY List
ORDER BY Senders DESC;

Use the temp table for the following query.

SELECT sender_count AS Sender Count, COUNT(mailing_list) AS Frequency
FROM al_temp_table
GROUP BY Sender Count
ORDER BY Frequency DESC;

What packages are required most by Free Software Foundation projects?

Description

This chart shows the packages that are required most frequently in FSF projects. There is a total of 8,024 requirements.

Visualization



SQL Script

SELECT requirement AS Requirement, COUNT(proj_num) AS Count
FROM fsf_project_requirements
WHERE datasource_id = <current>
GROUP BY Requirement
ORDER BY Count DESC;

What time of the day is email communication highest in Alioth projects?

Description

This chart shows the hourly breakdown of email communication in select Alioth projects. The projects shown had the highest total number of emails in their mailing lists.

Visualization:



SQL Script

SELECT COUNT(m.message_id), hour(m.date_sent)
FROM al_messages m
INNER JOIN al_mailing_lists l
ON l.mailing_list = m.mailing_list
WHERE l.project = 'project_name'
AND l.datasource_id = <current>
GROUP BY HOUR(m.date_sent)

What programming languages are paired together in Lanchpad projects (Network Diagram)?

Description

This graph shows a network diagram of the programming languages paired together by projects in Launchpad. The top 100 combinations are represented in the visual.

Visualization

LP Network Diagram


For an interactive visual, click the image below and then click the "Recompute layout" button (connected dots).


What programming languages are paired together in Launchpad projects?

Description

This chart shows the top programming languages pairs used by projects in Launchpad.

Visualization

LP Lang Pairs


What is the status of current Alioth projects?

Description

This chart shows the status breakdown of Alioth projects.

Visualization:



SQL Script

SELECT status AS Status, COUNT( unixname ) AS Count
FROM al_projects_status
WHERE datasource_id = <current>
GROUP BY Status
ORDER BY Count DESC;

What are the most common programming languages used by projects listed in Alioth?

Description

This chart shows the top programming languages used by projects in Alioth.

Visualization:



SQL Script

SELECT language AS Lang, COUNT( unixname ) AS Count
FROM al_projects_language
WHERE datasource_id = <current>
GROUP BY Lang
ORDER BY Count DESC;

Syndicate content