It is aimed to manipulate some data collected by Excell table by using SQLiteStudio program. In this context, four questions were tried to be answered and the answers to these questions were tried to be reached with sql codes.

I. CREATING TABLE AND INSERT THE DATA

/*All codes here were written and tested in SQLiteStudio (3.3.3) */

a. case_ad_statistics

/*This line has been added in case there is the same table in the database to be on the safe side. Delete the table before adding it to avoid conflicts. */

DROP TABLE IF EXISTS case_ad_statistics; 

/*Create a case_ad_statistics table and inserting the variables into it. */
/* The sent valuables are converted to usable lines in SQLiteStudio with Excel methods. */

CREATE TABLE case_ad_statistics(
AD_ID INT,
EVENT_DATE Date,
DELIVERIES INT,
IMPRESSIONS INT,
CLICKS INT,
SPEND INT
);
INSERT INTO case_ad_statistics (
AD_ID,
EVENT_DATE,
DELIVERIES,
IMPRESSIONS,
CLICKS,
SPEND
)
VALUES
(415050,'01.11.18',0,1,0,0),
(445347,'23.11.18',116,25,0,0),
(425498,'01.11.18',208,62,0,0),
(417943,'06.11.18',713,168,0,0),
(140447,'06.11.18',30,11,0,0),
(446094,'19.11.18',37042,1528,0,0),
(440554,'20.11.18',1427,304,3,5.04),
(440554,'24.11.18',2537,486,2,18.94),
(427747,'22.11.18',14006,1459,9,54),
(427747,'01.11.18',754,166,0,0),
(443025,'22.11.18',3829,892,0,0),
(429153,'10.11.18',562,168,0,0),
(401033,'03.11.18',2232,59,0,0),
(155408,'15.11.18',33,11,0,0),
(442486,'21.11.18',456,59,0,0),
(435393,'06.11.18',17908,1327,6,18.75),
(155408,'12.11.18',0,1,0,0),
(427747,'15.11.18',13552,2418,6,52.2),
(155408,'05.11.18',187,60,0,0),
(440554,'17.11.18',1626,254,2,18.83),
(439276,'25.11.18',1410,423,0,2.76),
(436213,'15.11.18',725,168,0,0),
(417943,'01.11.18',846,166,0,0),
(440554,'21.11.18',8721,2626,1,60),
(445347,'12.11.18',9,5,0,0),
(433391,'05.11.18',38,11,0,0),
(155408,'11.11.18',24,11,0,0),
(417943,'09.11.18',932,162,0,0),
(432689,'09.11.18',34,11,0,0),
(446766,'22.11.18',181,25,0,0);

/* check if the data is stored in the table. */

SELECT * FROM case_ad_statistics;

/* THE RESULT: */

b. case_ads

/*This line has been added in case there is the same table in the database to be on the safe side. Delete the table before adding it to avoid conflicts. */

DROP TABLE IF EXISTS case_ads;

/*Create a case_ads table and inserting the variables into it. */
/* The sent valuables are converted to usable lines in SQLiteStudio with Excel methods. */

CREATE TABLE case_ads(
AD_ID INT,
AD_TYPE VARCHAR(50),
CREATION_DATE DATE,
CLIENT_ID INT
);
INSERT INTO case_ads (
AD_ID,
AD_TYPE,
CREATION_DATE,
CLIENT_ID
)
VALUES
(442486,'Type C','25.10.18',408),
(429153,'Type A','25.10.18',402),
(425498,'Type C','27.10.18',409),
(440554,'Type B','23.10.18',401),
(432689,'Type B','27.10.18',402),
(427747,'Type A','29.10.18',407),
(446766,'Type A','31.10.18',402),
(140447,'Type A','25.10.18',401),
(443025,'Type A','24.10.18',401),
(445347,'Type E','25.10.18',401),
(417943,'Type C','25.10.18',404),
(415050,'Type A','25.10.18',410),
(433391,'Type A','30.10.18',402),
(436213,'Type B','29.10.18',405),
(155408,'Type A','25.10.18',402),
(439276,'Type D','25.10.18',402),
(446094,'Type B','28.10.18',406),
(435393,'Type A','25.10.18',403),
(401033,'Type A','25.10.18',403);

/* check if the data is stored in the table. */

SELECT * FROM case_ads;

/* THE RESULT: */

II. QUESTION AND ANSWERS

1. How Many Clients with Ads Do We Have in Our Database?

/*First, with the aggregate function of “COUNT” start to count whole data in the column of CLIENT_ID, but the total number was higher than 10 which is real, because duplicated CLIENT_ID is also counting. A customer may have placed more than one ad. That’s why the statement of DISTINCT was used to count only different/unique
CLIENT_ID. */

SELECT
COUNT(DISTINCT CLIENT_ID) AS “UNIQUE NUMBER OF CUSTOMER” FROM case_ads;

/* THE RESULT: 10 */

2. How Many Ads Do We Have in Our Database?

/*There are two ways to calculate the number of ads: */
/* The first way is making calculation in the case_ads and using COUNT aggregate belongs to the AD_ID. */

SELECT COUNT(AD_ID) AS "Total number of Ads" FROM case_ads;

/* The second way is making calculation in the table of case_ad_statics. But here DISTINCT statement had to be used because the table has a lot of duplicated entries with same AD_ID because the database is keeping updated depends on click or other factors. */

SELECT COUNT(DISTINCT AD_ID) AS "Total Ads" FROM
case_ad_statistics;

/* THE RESULT: */

19

3. Join The Two Tables in A Way That Sounds Appropriate

/*For the JOIN clause, the AD_ID column is used. This is because it was common column for both tables. */
/* The relation between the two tables above is the AD_ID. That’s why the column is used. */

SELECT
s.EVENT_DATE AS "Event Date",
c.CLIENT_ID AS "Customer ID",
c.AD_TYPE AS "Type",
c.CREATION_DATE AS "Creation Date",
s.DELIVERIES,
s.IMPRESSIONS,
s.CLICKS,
s.SPEND
FROM case_ads AS c
JOIN case_ad_statistics AS s ON s.AD_ID = c.AD_ID
ORDER BY EVENT_DATE ASC;

4. How Much Spend on Aggregate Do We Have Per Ad Type?

/*The SUM() function is used to calculate the total budged allocated by ad types. Inside of the SUM() functions a CASE WHEN statement was written according to each ad type.*/

SELECT
SUM(CASE WHEN c.AD_TYPE='Type A' THEN s.SPEND ELSE NULL
END) as "Type A",
SUM(CASE WHEN c.AD_TYPE='Type B' THEN s.SPEND ELSE NULL
END) as "Type B",
SUM(CASE WHEN c.AD_TYPE='Type C' THEN s.SPEND ELSE NULL
END) as "Type C",
SUM(CASE WHEN c.AD_TYPE='Type D' THEN s.SPEND ELSE NULL
END) as "Type D",

/*The following CASE WHEN statement maps the types in the case_ads table defined as c and sums the SPEND column corresponding to AD_ID from the case_ad_statistics table defined as s. The CASE WHEN statement will return NULL if/when there is a mistake or there is no match in the types. */

SUM(CASE WHEN c.AD_TYPE='Type E' THEN s.SPEND ELSE NULL
END) as "Type E"
/*Two tables are associated with JOIN with AD_ID that is common for
both tables. */
FROM case_ad_statistics AS s
JOIN case_ads AS c ON s.AD_ID = c.AD_ID;

/*RESULT: Type A: 124.95, Type B: 102.81, Type C: 0, Type D: 2.76,
Type E: 0 */