Getting monthly data with latest records in oracle

In this blog I am going to show how we can get monthly data for a given set of records. In many cases we try to get the data into reports with monthly , weekly etc. To achive this we can use N number of tools which are provided but sometime we have to write it because there is a need by other teams or external sources to make use of these records. Let us take an small example for my table which has the records something like as below :

 

From the above result set we need to pull only the yellow colored once. Why only the yellow , the query that we need to build is like to get the audits for every month. Now if the same audit is performed in two months multiple times with changed status then we need the latest record for each month.

Here are some sample tables with insert scripts:


create table xx_audit_tab
(
AUDIT_ID VARCHAR2(4000)
,AUDIT_TITLE VARCHAR2(4000)
,MON VARCHAR2(12 BYTE)
,AUDIT_STATUS VARCHAR2(1020)
,AUDIT_CLASSIFICATION VARCHAR2(4000)
,AUDIT_TYPE VARCHAR2(4000)
,INSTANCE_ID NUMBER
,CREATION_DATE DATE
,STAGE_NAME VARCHAR2(120)
,MAX_DATE DATE );

Insert statement for sample data :

begin
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
commit;
end;

 

To get the desired result we need to use “ROW_NUMBER” an analytical function .

SQL code:


select * from (
SELECT *
FROM (SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY mon,audit_id ORDER BY creation_date DESC) RN
FROM xx_audit_tab t
)sub
WHERE RN = 1 )
order by audit_id
;

 

 

Data Mining for Predictive Social Network Analysis

Social networks, in one form or another, have existed since people first began to interact. Indeed, put two or more people together and you have the foundation of a social network. It is therefore no surprise that, in today’s Internet-everywhere world, online social networks have become entirely ubiquitous.

Within this world of online social networks, a particularly fascinating phenomenon of the past decade has been the explosive growth of Twitter, often described as “the SMS of the Internet”. Launched in 2006, Twitter rapidly gained global popularity and has become one of the ten most visited websites in the world. As of May 2015, Twitter boasts 302 million active users who are collectively producing 500 million Tweets per day. And these numbers are continually growing.

Given this enormous volume of social media data, analysts have come to recognize Twitter as a virtual treasure trove of information for data mining, social network analysis, and information for sensing public opinion trends and groundswells of support for (or opposition to) various political and social initiatives. Twitter Trend Topics in particular are becoming increasingly recognized as a valuable proxy for measuring public opinion.

social network analysis and data mining

This article describes the techniques I employed for a proof-of-concept that effectively analyzed Twitter Trend Topics to predict, as a sample test case, regional voting patterns in the 2014 Brazilian presidential election.

The Election

General presidential elections were held in Brazil on October 5, 2014. No candidate received more than 50% of the vote, so a second runoff election was held on October 26th.

In the first round, Dilma Rousseff (Partido dos Trabalhadores) won 41.6% of the vote, ahead of Aécio Neves(Partido da Social Democracia Brasileira) with 33.6%, and Marina Silva (Partido Socialista Brasileiro) with 21.3%. Rousseff and Neves contested the runoff on October 26th with Rousseff being re-elected by a narrow margin, 51.6% to Neves’ 48.4%. The analysis in this article relates specifically to the October 26th runoff election.

Partido dos Trabalhadores (PT) is one of the biggest political parties in Brazil. It is the political party for the current and former presidents, Dilma Roussef and Luis Inacio Lula da Silva. Partido da Social Democracia Brasileira (PSDB) is the political party of the prior president Fernando Henrique Cardoso.

Data Mining and Extracting Twitter Trend Topic Data

I began social media data mining by extracting Twitter Trend Topic data for the 14 Brazilian cities for which data is supplied via the Twitter API, namely: Brasília, Belém, Belo Horizonte, Curitiba, Porto Alegre, Recife, Rio de Janeiro, Salvador, São Paulo, Campinas, Fortaleza, Goiânia, Manaus, and São Luis.

I queried the Twitter REST API to get the top 10 Twitter Trend Topics for these 14 cities in a 20 minute interval (limited by some restrictions that Twitter has on its API). Limiting the query to these 14 cities is done by specifying their Yahoo! GeoPlanet WOEIDs (Where On Earth IDs).

For this proof-of-concept, I used Python and a Twitter library (cleverly called “twitter”) to get all the social network data for the day of the runoff election (Oct 26th), as well as the two days prior (Oct 24th and 25th). For each day, I performed about 70 different queries to help identify the instant trend topics.

Below is an example of the JSON object returned in response to each query (this example was based on a query for data on October 26th at 12:40:00 AM, and only shows the data for Belo Horizonte).

[{"created_at": "2014-10-26T02:32:59Z",
  "trends":
	[{"url": "http://twitter.com/search?q=%23GolpeNoJN",
	  "name": "#GolpeNoJN", "query": "%23GolpeNoJN", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%23SomosTodosDilma",
	  "name": "#SomosTodosDilma", "query": "%23SomosTodosDilma", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%23EAecio45Confirma",
	  "name": "#EAecio45Confirma", "query": "%23EAecio45Confirma", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Uilson",
	  "name": "Uilson", "query": "Uilson", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%22Lucas+Silva%22",
	  "name": "Lucas Silva", "query": "%22Lucas+Silva%22", "promoted_content": null}, 
	 {"url": "http://twitter.com/search?q=%22Marcelo+Oliveira%22",
	  "name": "Marcelo Oliveira", "query": "%22Marcelo+Oliveira%22", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Cruzeiro",
	  "name": "Cruzeiro", "query": "Cruzeiro", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Tupi",
	  "name": "Tupi", "query": "Tupi", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%22Real+x+Bar%C3%A7a%22",
	  "name": "Real x Bar\u00e7a", "query": "%22Real+x+Bar%C3%A7a%22", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Wanessa",
	  "name": "Wanessa", "query": "Wanessa", "promoted_content": null}
	],
  "as_of": "2014-10-26T02:40:03Z",
  "locations": [{"name": "Belo Horizonte", "woeid": 455821}]
}]

Brief Intro to Social Network Analysis

Social Network Theory is the study of how people, organizations, or groups interact with others inside their network. There are three primary types of social networks:

  • Egocentric networks are connected with a single node or individual (e.g., you and all your friends and relatives).
  • Socio-centric networks are closed networks by default. Two commonly-used examples of this type of network are children in a classroom or workers inside an organization.
  • Open system networks are networks where the boundary lines are not clearly defined, which makes this type of network typically the most difficult to study. The type of socio-political network we are analyzing in this article is an example of an open system network.

Social networks are considered complex networks, since they display non-trivial topological features, with patterns of connection between their elements that are neither purely regular nor purely random.

Social network analysis examines the structure of relationships between social entities. These entities are often people, but may also be social groups, political organizations, financial networks, residents of a community, citizens of a country, and so on. The empirical study of networks has played a central role in social science, and many of the mathematical and statistical tools used for studying networks were first developed in sociology.

Establishing the Network

To create a network using the Twitter Trend Topics, I defined the following rules:

  • Each city is a vertex (i.e., node) in the network.
  • If there is at least one common trend topic between two cities, there is an edge (i.e., link) between those cities.
  • Each edge is weighted according to the number of trend topics in common between those two cities (i.e., the more trend topics two cities have in common, the heavier the weight that is attributed to the link between them).

For example, on October 26th, the cities of Fortaleza and Campinas had 11 trend topics in common, so the network for that day includes an edge between Fortaleza and Campinas with a weight of 11:

In addition, to aid the process of weighting the relationships between the cities, I also considered topics that were not related to the election itself (the premise being that cities that share other common priorities and interests may be more inclined to share ……continue reading…..

 

 

 

 

Thank you for reading my blog , the reference is taken from one of the article that was published by Elder Santos on Toptal. Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Installing RStudio & R on Windows 8 Operating System

 

RStudio integrated development environment is used for the user interface for R.

To install “RStudio” first open URL: http://www.rstudio.com/ide/

image

Now click on the Download button.  The latest version of RStudio is v0.98

Once you click on download it will ask you to choose one option out of two i.e. to Download RStudio desktop or RStudio server.

image

Select “Download RStudio desktop

Now you will see the options to choose the platform of your operating system and download,  but here I am going to use the below version:

From the website you will also get an suggestion recommended for your OS as like below:

image

I am selecting the option recommended i.e. “RStudio 0.98.501 – Windows XP/Vista/7/8

image

You can directly click on run button from the popup.

Then you installation process will be started.

image

Click Next.

image

Select any path where you want to install the software , here I am keeping as default selection.

image

Click on Install.

image

image

Finally click on Finish button.

Now you can

image

Now you can see that RStudio is been installed on my Windows 8 operating system.

Click on RStudio .

image

As my OS is 64 bit so I will keep as default and click on ok button.

image

If R is not installed on the OS earlier then we will see the message like above. So as I don’t have R installed prior to this installation so clicking on Yes.

Now for installing R navigate to URL : http://www.r-project.org/

 

image

Now click on CRAN link under download section as shown in above screen shot.

image

Here you can see the links to download. But I am going to choose the mirror from India (optional as you can select any other one too).

image

Click on the link :

image

Choose Download R for Windows option as shown in above screen shot.

image

Now click on the link “install R for the first time.

image

Now click on Download R 3.0.2 for Windows.

image

Now click on run .

 

image

Once downloaded then execute the .exe file to install R.

image

Click ok.

image

Click  : Next

image

Click Next

image

I am keeping as default path but you change it too.

image

Keep default selection and click ok next.

image

For startup option you can choose default and click on next.

image

Click Next :

image

Use default and click on next.

image

image

Finally R is been successfully installed on windows 8 but now lets check if everything works fine or not. Double click on the R icon created after installation. The you will see the screen as below:

image

 

image

You can see that I have given the input to do a addition for “1+1” which prints the value as “2”.

Now let us go back to our RStudio installation:

image

Now click on OK button.

image

image

image

Now finally we can see that R and RStudio are installed successfully on Windows 8 operating system.  Using RStudio is litter user friendly because we can save the codes as you can see from the above screen shot at the top left hand side and below will be the console where it is similar what we have seen earlier while opening R tool.