The City Of Philadelphia Business Intelligence interview guide, interview questions, salary data, and interview experiences.

City Of Philadelphia Business Intelligence Interview Guide

<p>Getting ready for an Business Intelligence interview at City Of Philadelphia? The City Of Philadelphia Business Intelligence interview span across 10 to 12 different question topics. In preparing for the interview:</p>
<ul>
<li>Know what skills are necessary for City Of Philadelphia Business Intelligence roles.</li>
<li>Gain insights into the Business Intelligence interview process at City Of Philadelphia.</li>
<li>Practice real City Of Philadelphia Business Intelligence interview questions.</li>
</ul>
<p>Interview Query regularly analyzes interview experience data, and we've used that data to produce this guide, with sample interview questions and an overview of the City Of Philadelphia Business Intelligence interview.</p>


Getting ready for an Business Intelligence interview at City Of Philadelphia? The City Of Philadelphia Business Intelligence interview span across 10 to 12 different question topics. In preparing for the interview:

- Know what skills are necessary for City Of Philadelphia Business Intelligence roles.
- Gain insights into the Business Intelligence interview process at City Of Philadelphia.
- Practice real City Of Philadelphia Business Intelligence interview questions.

Interview Query regularly analyzes interview experience data, and we've used that data to produce this guide, with sample interview questions and an overview of the City Of Philadelphia Business Intelligence interview.
        

Overview

City Of Philadelphia Business Intelligence Salary

<p>We've gathered this data from parsing thousands of interview experiences sourced from members.</p>

We've gathered this data from parsing thousands of interview experiences sourced from members.

City Of Philadelphia Business Intelligence Interview Process

<p>We’re given a table of bank transactions with three columns, <code>user_id</code>, a deposit or withdrawal value (determined if the value is positive or negative), and <code>created_at</code> time for each transaction.</p>

<p>Write a query to get the total three-day rolling average for deposits by day.</p>

<p><em>Note: Please use the format <code>&#39;%Y-%m-%d&#39;</code> for the date in the outout</em></p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>bank_transactions</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>transaction_value</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>dt</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>rolling_three_day</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>


Rolling Bank Transactions

<p>Write a query to identify the manager with the biggest team size.</p>

<p>You may assume there is only one manager with the largest team size.</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>employees</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>manager_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>managers</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>team</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>manager</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>team_size</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>


Manager Team Sizes

<p>Write a query to show the number of users, number of transactions placed, and total order amount per month in the year 2020. Assume that we are only interested in the monthly reports for a single year (January-December).</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>transactions</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>product_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>quantity</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>products</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>price</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<p><code>users</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>sex</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>month</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>num_customers</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>num_orders</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>order_amt</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>


Monthly Customer Report

City Of Philadelphia Business Intelligence Interview Questions

<h3>Job Description</h3><div>Job Description<div>Company Description<br><br><p>A best-in-class city that attracts best-in-class talent, Philadelphia is an incredible place to build a career. From our thriving arts scene and rich history to our culture of passion and grit, there are countless reasons to love living and working here. With a workforce of over 30,000 people, and more than 1,000 different job categories, the City of Philadelphia offers boundless opportunities to make an impact.</p><p>As an employer, the City of Philadelphia values inclusion, integrity, innovation, empowerment, and hard work above all else. We offer a vibrant work environment, comprehensive health care and benefits, and the experience you need to grow and excel. If you’re interested in working with a passionate team of people who care about the future of Philadelphia, start here.</p><p><em><strong>What we offer</strong></em></p><ul><li><strong>Impact </strong>- The work you do here matters to millions. </li><li><strong>Growth</strong> - Philadelphia is growing, why not grow with it? </li><li><strong>Diversity & Inclusion</strong> - Find a career in a place where everyone belongs.</li><li><strong>Benefits</strong> - We care about your well-being.</li></ul><p><strong><em>Agency Description</em></strong></p><p>The mission of the Department of Revenue is to collect tax and non-tax revenue due to the City and tax revenue due to the School District of Philadelphia while providing service to customers that is fair and efficient and ensuring that the City provides services that the residents can see, touch, and feel. The Department’s collections enable the Administration to invest in the Mayor’s strategic priorities, including housing, education, and making Philadelphia the safest, cleanest, and greenest big city in the nation with economic opportunity for everyone.</p>Job Description<br><br><p><strong>Position Summary:</strong></p><p>The Business Analyst or (Philadelphia Revenue Information System Manager (PRISM) Support Liaison (PSL)) will join an established, experienced, and dynamic team responsible for the continuous improvement of PRISM, the Department of Revenue’s tax software system. The PRISM team works in a supportive and cooperative environment that values asking questions, new ideas, and continuous learning. PSLs develop expertise in a particular functional area of PRISM through teamwork and work with their counterpart subject matter experts in Department of Revenue operational units.</p><p>The PRISM Support Liaison is responsible for daily coordination with operations to collect, understand, and analyze information related to the organization and its processes to apply Revenue’s Tax System (PRISM) practicality. Work with the selected vendor to analyze processing problems so the vendor may develop and implement solutions to system issues. The position functions and primary subject matter expert to connect PRISM system functions with practical applications for end users. The position recommends solutions or alternative practices to improve systems or communication, including developing letters for taxpayers. Gathering and organizing information about problems or procedures to be improved and partnering with stakeholders to resolve problems is a significant aspect of the work.</p><p>Day-to-day work includes responding to requests for system changes, testing software improvements before they are rolled out, and participating in design sessions for version updates. The PSL team also supports business users directly through consultation, needs assessment, troubleshooting, and training in partnership with the training team.</p><p><strong>Essential Functions:</strong></p><ul><li>Assist in <span class="long-word">creating/maintaining</span> Standard Operating Procedures for each unit.</li><li>Perform testing of SQRs or identification of appropriate testing resources.</li><li>Assist with the maintenance of training guides and help topics.</li><li>Participate in Letters and Forms Committees by applying PRISM subject matter expertise.</li><li>Coordinate with Revenue business units to collect, understand, and analyze data related to organizational processes and implementation of PRISM applications.</li><li>Work with Revenue business units to recommend, plan, prioritize, and implement changes for system improvement.</li><li>Build test data, identify resources, and perform testing of software, customer accounts, and scenarios that evaluate product functionality and imitate end-user experiences.</li><li>Perform quality assurance testing to</li><li>Verify the functionality of products, product updates, and new features before public release.</li><li>Ensure improvements meet organizational quality standards.</li><li>Identify and remedy operational defects within the software production processes before migration into production.</li><li>Collaborate with Revenue business units and external end-users to investigate, identify and validate customer complaints and product issues.</li><li>Facilitate and lead design sessions (including letters) to analyze work processes and develop strong requirements to meet stakeholder goals.</li><li>Analyze and design updates related to Gentax service pack enhancements, new versions of Gentax, business or legislative changes, tickets, and other projects.</li><li>Provide internal customer support for Revenue business units.</li><li>Communicate and advocate for business process changes that project goals and initiatives necessitate.</li><li>Work with users to ensure compliance and consistency in executing newly implemented processes.</li><li>Build and maintain collaborative and consultative relationships with system developers, Revenue business users, and OIT staff.</li></ul><p><strong>Competencies, Knowledge, Skills and Abilities:</strong></p><ul><li>Knowledge of business and personal taxes and how they are applied in a tax system.</li><li>Knowledge of laws, rules, regulations, and procedures concerned with municipal revenue collections.</li><li>Ability to prepare for and lead small group meetings through a defined agenda to achieve team goals.</li><li>Interest in analytically deconstructing issues and brainstorming potential solutions.</li><li>Flexibility for a dynamic project-like work environment where one day is never the same as the next</li><li>Desire to learn new things, grow skills, and support team members in doing the same.</li><li>Proficient in Microsoft Office applications and interested in learning new software tools.</li><li>Knowledge of Revenue collection operations, processes, and procedures</li><li>Skilled in informing and communicating with others on Revenue-related business or operational matters.</li><li>Skilled in conducting research and preparing reports.</li><li>Skilled in training personnel in organizational or compliance procedures</li><li>Ability to establish effective relationships with staff, vendors, strategic partners, the public, and their representatives.</li><li>Able to handle complaints, settle disputes, and resolve grievances and conflicts or otherwise negotiate with others.</li><li>Ability to build effective relationships and a strong commitment to working collegially and collaboratively with constituents at all levels in a diverse and distributed environment.</li><li>Ability to provide guidance and expert advice to management or other groups on technical, systems, or process-related topics.</li></ul>Qualifications<br><br><p>The ideal candidate will have at least three years of experience in a large organization or division involved in revenue collection, working on and/or leading project teams.</p><br><br>Additional Information<br><br><p><strong>TO APPLY:</strong> <strong>Interested candidates must submit a resume.</strong></p><p>Salary Range: $60,000 - $75,000</p><p><strong>Discover the Perks of Being a City of Philadelphia Employee:</strong></p><ul><li><strong>Transportation</strong>: City employees get unlimited <strong>FREE</strong> public transportation all year long through SEPTA’s Key Advantage program. Employees can ride on SEPTA buses, subways, trolleys, and regional rail for their daily commute and more.</li><li>Parental Benefits: The City offers its employees 6 weeks of paid parental leave.</li><li>We offer Comprehensive health coverage for employees and their eligible dependents.</li><li>Our wellness program offers eligibility into the discounted medical plan</li><li>Employees receive paid vacation, sick leave, and holidays</li><li>Generous retirement savings options are available</li><li><strong>Pay off your student loans faster</strong> - As a qualifying employer, City of Philadelphia employees are eligible to participate in the Public Service Loan Forgiveness program. Join the ranks of hundreds of employees who have already benefited from this program and achieved student loan forgiveness.</li><li><strong>Unlock Tuition Discounts and Scholarships</strong> - The City of Philadelphia has forged partnerships with over a dozen esteemed colleges and universities in the area, ensuring that our employees have access to a wide range of tuition discounts and scholarships. Experience savings of 10% to 40% on your educational expenses, extending not only to City employees but in some cases, spouse and dependents too!</li></ul><p>Join the City of Philadelphia team today and seize these incredible benefits designed to enhance your financial well-being and personal growth!</p><p><em><strong>*The successful candidate must be a city of Philadelphia resident within six months of hire</strong></em></p><p>Effective May 22, 2023, vaccinations are <strong>no longer required</strong> for new employees that work in non-medical, non-emergency or patient facing positions with the City of Philadelphia. As a result, only employees in positions providing services that  are patient-facing medical care (ex: Nurses, doctors, emergency medical personnel), must be fully vaccinated.</p><p>The City of Philadelphia is an Equal Opportunity employer and does not permit discrimination based on race, ethnicity, color, sex, sexual orientation, gender identity, religion, national origin, ancestry, age, disability, marital status, source of income, familial status, genetic information or domestic or sexual violence victim status. If you believe you were discriminated against, call the Philadelphia Commission on Human Relations at 215-686-4670 or send an email to faqpchr@phila.gov.</p><p>For more information, go to: Human Relations Website: <span class="long-word">http://www.phila.gov/humanrelations/Pages/default.aspx</span></p></div></div>

<strong>Company Description</strong><br><br>A best-in-class city that attracts best-in-class talent, Philadelphia is an incredible place to build a career. From our thriving arts scene and rich history to our culture of passion and grit, there are countless reasons to love living and working here. With a workforce of over 30,000 people, and more than 1,000 different job categories, the City of Philadelphia offers boundless opportunities to make an impact. <br>As an employer, the City of Philadelphia values inclusion, integrity, innovation, empowerment, and hard work above all else. We offer a vibrant work environment, comprehensive health care and benefits, and the experience you need to grow and excel. If you're interested in working with a passionate team of people who care about the future of Philadelphia, start here.<br>What We Offer:<br>•Impact - The work you do here matters to millions. <br>•Growth - Philadelphia is growing, why not grow with it? <br>•Diversity & Inclusion - Find a career in a place where everyone belongs.<br>•Benefits - We care about your well-being.<br><br>The Office of Innovation & Technology (OIT) is the central IT agency for the City of Philadelphia headed by the Chief Information Officer (CIO). OIT oversees all major information and communications technology initiatives for the City of Philadelphia - increasing the effectiveness of the information technology infrastructure, where the services provided are advanced, optimized, and responsive to the needs of the City of Philadelphia's businesses, residents, and visitors. OIT responsibilities include: identifying the most effective approach for implementing new information technology directions throughout city government; improving the value of the city's technology assets and the return on the city's technology investments; ensuring data security continuity; planning for continuing operations in the event of disruption of information technology or communications services; and supporting accountable, efficient and effective government across every city department, board, commission and agency.<br><br>The Free Library of Philadelphia (FLP) is one of the largest public library systems in the world. As an important cornerstone of the Philadelphia community, FLP has a mission to advance literacy, guide learning, and inspire curiosity. The long-term vision of the FLP is to build an enlightened community devoted to lifelong learning. For over 100 years, the FLP has championed education in and out of the classroom, providing no cost resources for literacy and learning.<br><br><strong>Job Description</strong><br><br>The Free Library of Philadelphia is looking for an experienced Data Engineer to join the Research & Data Analytics group. The Data Engineer will be responsible for designing, developing, and maintaining data architecture and infrastructure that enable efficient collection, storage, and analysis of data to support Performance Management objectives. <br>The Data Engineer will work closely with data scientists, analysts, and other team members to ensure that data is available, reliable, and ready for analysis. Expertise in data pipelines, ETL (Extract, Transform, Load) processes, and data warehousing will be essential in this role.<br>Beyond being a key asset to enterprise integration and open data initiatives, the Data Engineer will help develop and support the creation of Open Data initiatives for the Free Library's data sharing objectives by integrating appropriate City, state, and federal datasets.<br><br>Essential Functions<br>•Design, build, and maintain data pipelines to automate extraction, transformation, and loading of data from diverse sources.<br>•Develop and maintain data models that support Performance Management objectives, including designing schema structures, data dictionaries, and defining relationships.<br>•Integrate data from multiple sources, such as databases, APIs, logs, and third-party data providers, into a unified and accessible format.<br>•Develop and optimize ETL processes to ensure data quality, consistency, and availability. Handle data cleansing, enrichment, and transformation as needed.<br>•Maintain and optimize data warehousing solutions (e.g., SQL databases, NoSQL databases) to ensure efficient data storage and availability.<br>•Monitor and fine-tune data pipelines and database systems for optimal performance, scalability, and reliability.<br>•Implement data security and privacy measures to protect sensitive information, ensure compliance with appropriate data safety regulations, and establish access controls.<br>•Create and maintain documentation for data engineering processes, data models, and data flow diagrams to facilitate understanding and collaboration.<br>•Collaborate with data analysts, software engineers, and FLP Leadership to understand data requirements and provide support for data-driven decision making.<br>•Implement data governance practices, including data lineage, metadata management, and data cataloging.<br><br>Competencies, Knowledge, Skills and Abilities<br><br>Required:<br>•Proficiency in programming languages (R, Python) and database query languages (SQL).<br>•Knowledge of ETL tools and data integration techniques.<br>•Experience with data warehousing solutions.<br>•Familiarity with big data technologies (Hadoop, Spark) is a plus.<br>•Experience with Linux server administration.<br>•Strong problem-solving skills and attention to detail.<br>•Excellent communication and collaboration skills.<br>Preferred:<br>•Experience with cloud platforms (e.g., AWS, Azure, Google Cloud).<br>•Experience handling regulatory requirements related to PII.<br>•Knowledge of containerization and orchestration tools (e.g., Docker, Kubernetes).<br>•Understanding of data streaming technologies (e.g., Kafka, Apache Flink).<br>•Familiarity with data governance and data cataloging tools.<br><br><strong>Qualifications</strong><br><br>•Bachelor's or higher degree in Computer Science, Information Technology, Information Science, or a related field.<br>•3 years experience as a Data Engineer or similar role.<br><br><strong>Additional Information</strong><br><br>Salary Range: $75,000 - $85,000<br><br>Starting salary to be determined based on experience and qualifications.<br><br>Important: To apply, candidates must provide a cover letter and a resume.<br><br><strong>Discover the Perks of Being a City of Philadelphia Employee</strong>:<br>•We offer Comprehensive health coverage for employees and their eligible dependents<br>•Our wellness program offers eligibility into the discounted medical plan<br>•Employees receive paid vacation, sick leave, and holidays<br>•Generous retirement savings options are available<br>•<strong>Pay off your student loans faster </strong>- As a qualifying employer, City of Philadelphia employees are eligible to participate in the Public Service Loan Forgiveness program. Join the ranks of hundreds of employees who have already benefited from this program and achieved student loan forgiveness.<br>•<strong>Enjoy a Free Commute on SEPTA</strong> - Starting September 1, 2023, eligible City employees will no longer have to worry about paying for SEPTA public transportation. Whether you're a full-time, part-time, or provisional employee, you can seize the opportunity to sign up for the SEPTA Key Advantage Program and receive free Key cards for free rides on SEPTA buses, trains, trolleys, and regional rails.<br>•<strong>Unlock Tuition Discounts and Scholarships </strong>- The City of Philadelphia has forged partnerships with over a dozen esteemed colleges and universities in the area, ensuring that our employees have access to a wide range of tuition discounts and scholarships. Experience savings of 10% to 40% on your educational expenses, extending not only to City employees but in some cases, spouse and dependents too!<br>Join the City of Philadelphia team today and seize these incredible benefits designed to enhance your financial well-being and personal growth!<br><br>*The successful candidate must be a city of Philadelphia resident within six months of hire<br><br>Effective May 22, 2023, vaccinations are no longer required for new employees that work in non-medical, non-emergency or patient facing positions with the City of Philadelphia. As a result, only employees in positions providing services that are patient-facing medical care (ex: Nurses, doctors, emergency medical personnel), must be fully vaccinated.<br><br>The City of Philadelphia is an Equal Opportunity employer and does not permit discrimination based on race, ethnicity, color, sex, sexual orientation, gender identity, religion, national origin, ancestry, age, disability, marital status, source of income, familial status, genetic information or domestic or sexual violence victim status. If you believe you were discriminated against, call the Philadelphia Commission on Human Relations at 215-686-4670 or send an email to faqpchr@phila.gov.

City Of Philadelphia Business Intelligence Interview Guide

Overview

City Of Philadelphia Business Intelligence Salary

City Of Philadelphia Business Intelligence Interview Process

City Of Philadelphia Business Intelligence Interview Questions

City Of Philadelphia Business Intelligence Jobs